# Count number of rows that have X in cell A or B or C

Posted on

QUESTION :

I think looking at the photo is the easiest way to show what I need:

Original Table

How do I set up a single formula to count the number of kids who failed an exam section?

What I’ve managed so far is to create an extra column and set up some nested IF statements:

Extra Column

Then I can tally that E column. However, I’m trying to figure this out with creating an extra column. I’m stumped. I assume there has to be some CountIF and a range in the formula at the start.

Thank you!

You could use a rather complex formula:

``````=SUM(N(MMULT(N(B3:D10="Fail"),ROW(INDEX(A:A,1):INDEX(A:A,COLUMNS(B3:D10)))^0)>0))
``````

If you want to shorten this and don’t mind an array-entered variant:

``````=SUM(N(MMULT(N(B3:D10="Fail"),TRANSPOSE(COLUMN(B3:D10)^0))>0))
``````

Confirmed through CtrlShiftEnter

If you want to learn why/how these formula’s work I suggest you read through this link

EDIT

Another option would be to use `SUMPRODUCT` like so:

``````=SUMPRODUCT(--((B3:B10="Fail")+(C3:C10="Fail")+(D3:D10="Fail")>0))
``````

Which would make the whole thing a bit more understandable perhaps.

This Array Formula will solve the issue:

Formula to Count `Fail` in Cell `F142`:

``````{=SUM(--(MMULT(--(\$C\$136:\$E\$141=\$E\$142),TRANSPOSE(COLUMN(\$C\$136:\$E\$141)))>0))}
``````

N.B.

• Formula in Cell `F142`, finish with Ctrl+Shift+Enter.
• Criteria Fail in Cell `E142`, makes the Formula Dynamic.

• MMULT returns the Matrix product of two Array.
• `(--(\$C\$136:\$E\$141=\$E\$142)`, generates a `TRUE/ FALSE` for every value in Range.
• The Double negative `--` coerces the `TRUE/FALSE` into `1` & `0` like this.

`{1,1,1;0,1,0;1,0,1;0,0,0;0,0,1;1,0,0}`

• `TRANSPOSE(COLUMN(\$C\$136:\$E\$141))`, is tricky part of the Formula, where COLUMN is used to generate numeric array of the right size.
• For Non-zero with `>0` & again coerce `TRUE/ FALSE` into `1` and `0`gets final array inside SUM.

`=SUM({1;1;1;0;1;1})`, returns `5`.

You may adjust cell references in the Formula as needed.