Need help with counting value formula

Solved/Closed
Report
Posts
3
Registration date
Friday June 17, 2016
Status
Member
Last seen
June 17, 2016
-
Posts
3
Registration date
Friday June 17, 2016
Status
Member
Last seen
June 17, 2016
-
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

Posts
3
Registration date
Friday June 17, 2016
Status
Member
Last seen
June 17, 2016
1
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
Posts
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
148
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
Posts
3
Registration date
Friday June 17, 2016
Status
Member
Last seen
June 17, 2016
1
"1 - Critical" I'll have to look at speedyshare later as it's on my work PC
0