SOLVED

Writing an IF Formula in Excel with multiple conditions over multiple cells

Copper Contributor

I am trying to write an IF formula in excel that has multiple conditions from multiple cells.

Columns C, D, E, & F are conditional formatted so that if the value in each of them is <300 the cell fills with a light green color, if the value is between 301-700 the cell fills with a yellow color, and if the value is >700 the cell fills with an orange color.  Green fill color indicates the count is low, yellow fill color indicates the count is medium, and orange fill color indicates the count is high. 

The Column I am trying to write the IF formula into is Column G and I need it to put either the letter N or the letter Y (for No or Yes) into the cells of Column G depending on what the values in Columns C-F are.  If all 4 cells (C-F) are each <300 then the cells in Column G should be N

If at least 2 cells (C-F) are >300 then the cells in Column G should be Y

I have written both an IF(AND) formula: IF((AND(C11>300,D11>300,E11>300,F11>300)),"Y","N")

as well as an IF(OR) formula: IF((OR(C13>300,D13>300,E13>300,F13>300)),"Y","N")

The IF(AND) formula works correctly at putting the N value into the cells of Column G only if one or none of the cells (C-F) is above 300, and will correctly put the Y value into the cells of Column G only if all 4 cells (C-F) are above 300.  However, if 2 or 3 of the cells (C-F) are above 300, it incorrectly puts the letter N by using the IF(AND) formula as it is written.

The IF(OR) formula works correctly at putting the Y value into the cells of Column G if two or more of the cells (C-F) are above 300 as well as correctly putting the N value into the cells of Column G only if all 4 cells (C-F) are below 300.  However, if only one of the cells (C-F) are above 300, then if incorrectly puts the letter Y instead of the letter N by using the IF(OR) formula as it is written.

Can someone help me figure out how to correct this so that I only have to use one formula for all the cells in Column G? 

8 Replies

@MelissaHammond 

In G10:

 

=IF(COUNTIF(C10:F10,">300")>=2,"Y","N")

 

Fill down.

Hey Hans, thanks for the reply, unfortunately, it didn't work correctly for all situations.
It works correctly for when all cell values (C-F) are less than 300, or if only 1 of the cells (C-F) is above 300, or if at least 3 of the cells (C-F) are above 300, but not when 2 of the cells (C-F) are above 300. However, if only two of the cells (C-F) have a value in them at all (example: C = 100, and E = 265, but there is no value in D or F) it put Y instead of N, and conversely (example: D = 308, and F = 482, but there is no value in C or E) it put N instead of Y.

@MelissaHammond 

I don't understand. See the screenshot below:

S0629.png

@Hans Vogelaar 

 

I don't understand either...in your example, it works, but when I applied it to mine, it did not.

Check out the Screenshot I took.

@MelissaHammond 

Could you attach a copy of your workbook instead of a screenshot? You can delete everything that is not relevant to the problem.

@Hans Vogelaar 

 

Here is a copy.

 

best response confirmed by allyreckerman (Microsoft)
Solution

@MelissaHammond 

Thanks. In the sample workbook in your first post in this thread, the data began in row 10, so I posted a formula for row 10.

But in your 'real' workbook, the data begin in row 11, but you still used the formula for row 10. Hence the result referred to the wrong row.

See the attached version.

Thank you Hans!! Something so small like that can easily be overlooked (as it obviously was by me, lol)...You've been a definite help!!
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@MelissaHammond 

Thanks. In the sample workbook in your first post in this thread, the data began in row 10, so I posted a formula for row 10.

But in your 'real' workbook, the data begin in row 11, but you still used the formula for row 10. Hence the result referred to the wrong row.

See the attached version.

View solution in original post