Need help with counting value formula

Solved/Closed
QAA007 Posts 3 Registration date Friday June 17, 2016 Status Member Last seen June 17, 2016 - Jun 17, 2016 at 03:23 PM
QAA007 Posts 3 Registration date Friday June 17, 2016 Status Member Last seen June 17, 2016 - Jun 17, 2016 at 04:16 PM
Hello, I am brand new. I am trying to figure out a score based counting formula.

I have columns with
Name, Status, Rank and Value

I want the value to populate based upon the list value selected in Status or Rank.

I have two sections, the first section has 3 options and shows the value fine.

The second section has 7 options and I cannot figure out how to cause this to count. I can't define all 7 values. I tried another route.

1 value is 15
1 value is 10
if any of the other values are selected it's 5
blank would be blank.

This is what I have so far:

=IF(ISERROR(SEARCH("*1 - Critical*",C7,1)),IF(ISERROR(SEARCH("*2 - Serious*",C7,1)),IF(ISERROR(SEARCH("*%*",C7,1)),"","5"),"10"),"15")


I tried % for a wild card. I tried C7 <>. Neither work.

What I need is if "Rank" is "critical", then "value" will show 15, if rank is serious, value shows 10 and if it's any other value than blank it's 5.



I hope I explained this well enough and I've attempted some webfu for several hours to no avail. Any help would be greatly appreciated.

3 replies

QAA007 Posts 3 Registration date Friday June 17, 2016 Status Member Last seen June 17, 2016 1
Jun 17, 2016 at 04:16 PM
I figured it out finally...

=VALUE(IF(ISERROR(SEARCH("*1 - Critical*",C7,1)),IF(ISERROR(SEARCH("*2 - Serious*",C7,1)),IF(ISTEXT(C7),"5","0"),"10"),"15"))

It works! and even allows me to total it into a cell. the:
IF(ISTEXT(C7),"5","0")

This worked for me, if it has a text value it's 5 if not it's 0. That is what I needed :D
1
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Jun 17, 2016 at 03:31 PM
QAA007, Good afternoon.

Is your text "1 - Critical" or "Critical" ?

Save your file at a free site, www.speedyshare.com and put a link to download here.

It will be easier to help you.
0
QAA007 Posts 3 Registration date Friday June 17, 2016 Status Member Last seen June 17, 2016 1
Jun 17, 2016 at 03:56 PM
"1 - Critical" I'll have to look at speedyshare later as it's on my work PC
0