Need help with counting value formula [Solved/Closed]

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.
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?  
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.
plus moins
"1 - Critical" I'll have to look at speedyshare later as it's on my work PC

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!