SOLVED

Need Nested IF Formula to Count Data Only, Not Formulas

Copper Contributor

Hello,

 

I cannot figure out how to get my formula to account for data returned from another cell, not it's formula.

 

I'll attempt to explain simply, I have sets of 5 water samples per fixture that have two different range results. <5 isn't accounted for, >5 - <14.99 is medium levels of danger, and >15 is high levels of danger. These sample results are in column A

 

In column B and C, I created a total count of each type of level with a COUNTIF function. Column B is medium (later noted with "YELLOW"), so if 2/5 samples are medium level, it will display 2, and column C is high (later noted with "RED"), so if 1/5 samples is high, it will display 1.

Here are the column B and C formulas:

"YELLOW"=COUNTIFS(A2:A6,">5",A2:A6,"<14.99")

"RED"=COUNTIF(A2:A6,">15")

 

Where it gets tricky is Column D, which I want to let me know if a single fixture contains "YELLOW", "RED", or "BOTH" sample levels within the set of 5.  I need to know this because we need to count how many fixtures specifically contain medium or high levels, not how many SAMPLES. You can see it would be confusing to say we had 3 samples with high levels, because it sounds like 3 different fixtures had high levels, when really all three samples were from a single fixture.

 

I have created a nested IF/AND statement that works perfectly when columns B and C are "plain" data with no COUNTIF formula calculating the number. However, I cannot do it that way and I need to use a formula to count the samples in each category. Here is the formula created:

=IF(AND(B2="",C2<>""),"YELLOW",IF(AND(B2<>"",C2=""),"RED",IF(AND(B2<>"",C2<>""),"BOTH","NEITHER")))

 

The problem I keep encountering is, this column D formula keeps counting the formula used in columns B and C as text, and the results keep returning as "BOTH", because the field is never really blank because of the formula inside it.

 

I need the column D formula to count the DATA ONLY in column B and C. Can anyone PLEASE help with this???

 

8 Replies

@stamez 

 

Hello,

It would be quite helpful if you share the file or some sample data. 

Thanks

@tauqeeracma Attached is a sample set! The only difference is the results column is now column Q

@tauqeeracma I forgot to change it back but I changed the sheet to leave a cell blank if the result was "0", and I still had the same problem.

best response confirmed by stamez (Copper Contributor)
Solution

@stamez 

First, apply to the column General format instead of Text. Formula is like

=IF(AND(Y2=0,Z2<>0),"YELLOW",IF(AND(Y2<>0,Z2=0),"RED",IF(AND(Y2<>0,Z2<>0),"BOTH","NEITHER")))

 

@Sergei Baklan Okay, I had it that way before and still the same issue, I was just changing up formatting types to see if it made a difference and it doesn't. Still the same problem.

@stamez 

Did you check the file I attached in previous post? It show different result.

@Sergei Baklan Oh! I'm sorry I didn't see you had actually adjusted the formula and not the column formatting. Yes, that solved the problem thank you so much!!!

1 best response

Accepted Solutions
best response confirmed by stamez (Copper Contributor)
Solution

@stamez 

First, apply to the column General format instead of Text. Formula is like

=IF(AND(Y2=0,Z2<>0),"YELLOW",IF(AND(Y2<>0,Z2=0),"RED",IF(AND(Y2<>0,Z2<>0),"BOTH","NEITHER")))

 

View solution in original post