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