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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1927
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
June 17, 2021
143
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.
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!