Excel: Help me use COUNTIFS to find A or B, but NOT A and B

Question

I have this questionnaire that has a score for answers in 3 rows (3 questionnaires). I need to find the ones that have either Q1 >=30 or Q2 >12 or Q3 <=33. I've been googling and trying solutions but failed when compared by counting manually.

See screenshot (rows AN / AT / BC would be the answers for Q1 / Q2 / Q3):

On rows BD/BE you can see my manual count, which was a PITA and is prone to error.

This formula helped me count ALL values including Q1+Q2+Q3:

=COUNTIFS(AN4:AN147;">=30";AT4:AT147;">=12";BC4:BC147;">=0"; BC4:BC147;"<=33")

What I need the formula for is to find instances where AN or AT or BC has a given value. For AN it's >=30, for AT it's >=12 and for BC it's <=33. For example, if one responder has 31(!), 10, 40, then he should be counted as "1" (or just counted), if one responder has 31(!), 15(!), 41; then he should also be counted as "1"), if one responder has 25), 10, 41; then he shouldn't be counted.

This is a burnout syndrome scale called Maslach Burnout Inventory, what I'm trying to get here are those at risk of burnout (must have 1 or 2 of those 3 sub-questionnaires altered, but not all 3 as those individuals have burnout syndrome)

Here's a sample using markdown generator as requested:

| AN    | AT    | BC    |       |       |
|----   |----   |----   |---    |---    |
| 14    | 11    | 41    |       |       |
| 14    | 4     | 43    |       |       |
| 50    | 9     | 41    |       |       |
| 38    | 16    | 20    |       |       |

edit: can't get to display it properly, hmm. edit2: got it

Answer 1

=SUMPRODUCT(N(((AN>=30)+(AT>=12)+((BC>=0)*(BC<=33)))={1,2}))

where AN, BC and AT are named ranges that refer to the obvious.

In your original formula, you also tested that BC>0 so I included it above.

However, if that test is not necessary, the formula can be shortened to:

=SUMPRODUCT(N(((AN>=30)+(AT>=12)+(BC<=33))={1,2}))

The Formula Evaluation tool can help you figure out what is going on. And in the screen shot below, the byRow column is not required. It is only there to demonstrate the results of the test on each line, for learning purposes.

• Each equality test returns an array of {TRUE,FALSE} depending on the results.
  • In Excel TRUE=1 and FALSE=0.
• Summing the arrays results in an array of {0,1,2,3} depending on how many matches there are in each row.
• We then compare those results to see if they are equal to 1 or 2 again returning another {TRUE, FALSE} array.
• The N function changes the {TRUE,FALSE} into {1,0} and SUMPRODUCT adds the all up.
• One could use the SUM function but then you have to remember that, with the array formula, you have to confirm the formula by holding down ctrl-shift while hitting enter to get the correct result.

Answer 2

Here is one way of doing it :

=SUMPRODUCT( ( ( (AN4:AN7>=30)+(AT4:AT7>12)+(BC4:BC7<=33) )=1)
+( ( (AN4:AN7>=30)+(AT4:AT7>12)+(BC4:BC7<=33) )=2) )

@Ron Rosenfeld's formula is a shorter and more elegant way of accomplishing the same thing.

Anyway if you consider some dummy data like that in the screenshot below which shows rows which satisfy 0,1,2 and 3 of the criteria respectively, my formula works as follows:

Row 4:

(FALSE+FALSE+FALSE = 1) => 0+0+0 = 1 => 0=1 => FALSE
(FALSE+FALSE+FALSE = 2) => 0+0+0 = 2 => 0=2 => FALSE
FALSE+FALSE => 0

Row 5:

(TRUE +FALSE+FALSE = 1) => 1+0+0 = 1 => 1=1 => TRUE
(FALSE+FALSE+FALSE = 2) => 0+0+0 = 2 => 0=2 => FALSE
TRUE+FALSE => 1

Row 6:

(TRUE +TRUE +FALSE = 1) => 1+1+0 = 1 => 2=1 => FALSE
(TRUE +TRUE +FALSE = 2) => 1+1+0 = 2 => 2=2 => TRUE
FALSE+TRUE => 1

Row 7:

(TRUE +TRUE +TRUE = 1) => 1+1+1 = 1 => 3=1 => FALSE
(TRUE +TRUE +TRUE = 2) => 1+1+1 = 2 => 3=2 => FALSE
FALSE+FALSE => 0

Sumproduct adds up the four totals.

---

There is another way of doing it (perhaps more for interest than practical use, but doesn't involve pseudo-array formulas):

=COUNTIF(AN4:AN7,">=30")+COUNTIF(AT4:AT7,">=12")+COUNTIF(BC4:BC7,"<=33")
-COUNTIFS(AN4:AN7,">=30",AT4:AT7,">=12")-COUNTIFS(AN4:AN7,">=30",BC4:BC7,"<=33")-COUNTIFS(AN4:AN7,">=12",BC4:BC7,"<=33")

The first line of the formula just adds up the individual questions that satisfy one of the criteria. But then in the case where two criteria are satisfied, we would have double counting. So we have to subtract one for each of those pairs. What about the case when three criteria are satisfied? We would end up subtracting one three times, which brings us back to zero, so that is OK.

Looking at the Venn diagram might help:

Note there is some evidence here that even multiple sumifs may be faster than sumproducts for this type of problem, but only relevant if you have a lot of data.