# Formula construction

#### Jon Johny

##### Board Regular
<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
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.

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### jasonb75

##### Well-known Member
If I've read it right, try

=IF(COUNTIF(C3:C5,"Overtime")>0,"Potential Risk",IF(COUNTIF(C3:C5,"Regular Season")>0,"Regular Season",IF(COUNTIF(C3:C5,"Playoffs")>0,"Playoffs",IF(COUNTIF(C3:C5,"NA")>0,"NA",""))))

#### Jon Johny

##### Board Regular
This formula works, but I really dont understand the logic. How does it understand, for example to put "Playoffs" in cell C2 - only when the entire range has the value "Playoffs". And the same would go for NA and ""

For 'Regular Season', only one of the fields in the range has to have 'Regular Season' for C2 to turn 'Regular Season'

And how does it know that if just one 'Overtime' is in the range - C2 will have to be 'Overtime'

Thanks,

Is it just the order that youv've constructed it in?

#### nbrcrunch

##### Well-known Member
Jon

Excel offers a feature to "Evaluate Formula." Click a cell containing the formula then click the option. it will walk you through it so that you can see how it works.

#### Jon Johny

##### Board Regular
I've used 'Evaluate Formula' option before. But in this formula, I dont udnerstand how it understands that a) the entire range must be 'playoffs' for c2 to go 'playoffs' and only 1 cell in the range has to be 'regular season' for C2 to go regular season. The fomula is constructed the same way in both cases, so i dont uderstand how it distinguishes.

#### jasonb75

##### Well-known Member
It's all done with the sequence of the formula, if it finds one cell in the range that matches the first "text" criteria then it stops and gives that result, if it doesn't find that result anywhere it moves to the next "text" criteria and the process is repeated until it eventually finds something that matches.

If there is no match by the time it checks the last criteria it leaves the cell blank.

Try looking at it as a series of events, blue text added in to make it easier to understand

IF(COUNTIF(C3:C5,"Overtime")>0,"Potential Risk", if not
IF(COUNTIF(C3:C5,"Regular Season")>0,"Regular Season", if not
IF(COUNTIF(C3:C5,"Playoffs")>0,"Playoffs", if not
IF(COUNTIF(C3:C5,"NA")>0,"NA", if not
""))))

Hope this helps

#### Jon Johny

##### Board Regular
Ok, but accrding to how this formula performed, all cells in the range had to be 'Playoffs', for C2 to go playoffs. And not just one cell as is the case with overtime. I dont understand this

#### jasonb75

##### Well-known Member
By using Countif()>0 the formula stops if it finds 1 or more cells in the range with that "text" in, so 1 or more counts of "overtime" would show "Potential Risk"

If there was no cell with "Overtime" then the formula moves to "Regular Season", again 1 a count of 1 or more cells with this would show that result, a count of 0 would move onto "Playoffs", count of 1 or more gives that result, count of 0 moves to "NA", count of 1 or more gives that result, count of 0 means no match so result is a blank cell.

#### shemayisroel

##### Well-known Member
Jon Johny,

jasonb75 solution seems like the right one, if not please post some sample data with expected results and I'm sure some one can post a solution up for you.

Replies
1
Views
72
Replies
1
Views
160
Replies
3
Views
195
Replies
8
Views
111
Replies
1
Views
191

1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

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