Report

Need help with counting value formula [Solved]

Ask a question QAA007 3Posts Friday June 17, 2016Registration date June 17, 2016 Last seen - Latest answer on Jun 17, 2016 04:16PM
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.
See more 
Helpful
+1
moins plus
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
Add comment
Helpful
+0
moins plus
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.
Add comment
Helpful
+0
moins plus
"1 - Critical" I'll have to look at speedyshare later as it's on my work PC
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!