 • 412K Members
• 4,219 Online
• 468K Conversations
SOLVED

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.

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

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies