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!

ANSWER :

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

enter image description here

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:

enter image description here

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 0gets final array inside SUM.

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

You may adjust cell references in the Formula as needed.

Leave a Reply

Your email address will not be published. Required fields are marked *