COUNTIF - evalauating a range

[Jon Johny]

I have a question concerning the COUNTIF function.

1)I want to create something below, in cell C2, which basically looks at the range C3:C5 and assigns a value. I have 1 rule which says that if anything in C3:C5 says 'Overtime' cell C2 should say potential risk. If these cells do not say 'Overtime' but has any cells in that range that say 'Regular Season', then make C2 'Regular Season'. Finally if all the cells in C3:C5 say 'Playoffs', than make C2 'Playoffs'.

2)One thing I did not mention, is if all the cells in the C3:C5 range say NA then cell C2 should be NA, if all the cells in the range are blank then C2 should be blank. If all the cells in the range are a mixture of just blank and 'NA', then C2 should be NA.

The formula I have up till now reads,
=IF(COUNTIF(C3:C5,"Overtime")>0,"Overtime",IF(COUNTIF(C3:C5,"Regular Season")>0,"Regular Season","Completed"))

However, I need to incorporate the part in paragraph 2.

Any help is appreciated.

 

[Peter_SSs]

There are some discrepancies between your description and your formula.

- Your description mentions "potential risk" but in the corresponding place your formula has "overtime"

- Formula mentins "Completed", description does not.


Also, your description does not cover all possibilities as I see it. here's one that seems to be missing:

- What happens if two of the cells are blank and one (or two) contains "Playoffs"?

 

[Jon Johny]

If there is an NA or blank, but complete in any of the cells, then C2 will go 'playoffs', which is what I want to happen.

If any of the cells contain 'regular season' blank, 'na; & 'playoffs', the cell will go 'regualr season'.

You are right about the descrepencies, id the cell says 'overtime', it should go 'overtime' and not 'potential risk'

 

[Peter_SSs]

I'm afraid I'm still struggling to put together a clear set of rules for what you want.

So, firstly is this a complete list of the possible entries in each of C3:C5?
overtime
complete
playoffs
regular season
(blank)
NA (is this text "NA" or the formula error value #N/A ?)

Secondly, let's try to make a clear set of rules and the order in which they should be applied. How would it be if we were to apply the rules below and in that order? In other words, as soon as we come to a rule that is true, we put the relevant value in C2 and we are finished. This list is probably wrong, but perhaps you can use it as a basis for giving a correct clear set of rules and/or point out in what circumstance my rules give the incorrect result or don't cover at all a particular set of values in C3:C5.

1. If anything in C3:C5 says 'overtime' cell C2 should be 'overtime'

2. If anything in C3:C5 says 'complete' cell C2 should be 'playoffs'

3. If all the cells in C3:C5 say 'playoffs' then cell C2 should be 'playoffs'

4. If anything in C3:C5 says 'regular season' or 'playoffs' cell C2 should be 'regular season'

5. If all the cells in C3:C5 are blank then cell C2 should be blank

7. If anything in C3:C5 says 'NA' then cell C2 should be 'NA'

 

[jasonb75]

Duplicate of http://www.mrexcel.com/forum/showthread.php?t=380370