Report

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

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!