**QUESTION :**

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

**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:

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

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 `Ctrl``Shift``Enter`

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.*