Need help with counting value formula [Solved]

Ask a question QAA007 3Posts Friday June 17, 2016Registration date June 17, 2016 Last seen - Last answered on Jun 17, 2016 at 04:16 PM by QAA007
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 
plus moins
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:

This worked for me, if it has a text value it's 5 if not it's 0. That is what I needed :D
Was this answer helpful?  
Leave a comment
plus moins
QAA007, Good afternoon.

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

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

It will be easier to help you.
Leave a comment
plus moins
"1 - Critical" I'll have to look at speedyshare later as it's on my work PC
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!