SOLVED

# Need Nested IF Formula to Count Data Only, Not Formulas

Occasional Contributor

# Need Nested IF Formula to Count Data Only, Not Formulas

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

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

Hello,

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

Thanks

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

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

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

@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 (Occasional Contributor)
Solution

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

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")))`

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

@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.

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

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

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

@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!!!

# Re: Need Nested IF Formula to Count Data Only, Not Formulas

@stamez , you are welcome