# COUNTIF - evalauating a range

#### Jon Johny

##### Board Regular
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.

Last edited:

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Peter_SSs

##### MrExcel MVP, Moderator

- 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

##### Board Regular
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

##### MrExcel MVP, Moderator
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'

Replies
1
Views
145
Replies
3
Views
148
Replies
6
Views
212
Replies
3
Views
277
Replies
2
Views
236

1,191,587
Messages
5,987,512
Members
440,098
Latest member
MickyMouse123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back