# Formula to use to add up all rows that returned a value of '1' for several cells that meet a specific criterion

I need cell `B21` in the `Reports` sheet to add up all rows that meet this criterion:

return a value of `1` if columns M, O, T, U, W, X, Z, AA, AB, AC have the answer `No, I do not have psoriasis on this area` in the `Failed` worksheet.

The formula I currently have only applies to row 2.

```=IF(
COUNTIF(
ARRAYFORMULA(
{Failed!M2,Failed!O2,Failed!T2:U2,Failed!W2:X2,Failed!Z2:AC2}
),
"No, I do not have psoriasis on this area"
)=10,
1,
0
)
```

How do I sum up all rows?

• As I understand the question, the result should be 1 if all the related cells are "No, I do not have psoriasis on this area" and 0 otherwise. Is that correct? Apr 12, 2016 at 13:32
• yes, that's right.
– Vee
Apr 12, 2016 at 14:10

This should work:

``````=SUM(ArrayFormula(N({Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC}="No, I do not have psoriasis on this area")))
``````

After some clarification this formula seems to have worked:

``````=COUNTIF(ArrayFormula(MMULT(N({Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC}="No, I do not have psoriasis on this area"), transpose(split(rept("1+", 10),"+")))), "=10")
``````
• The formula in this answer will return the number of cells with "No, I do not have psoriasis on this area" but, as I understand the question, the result should be 1 if all the related cells are "No, I do not have psoriasis on this area" and 0 otherwise. Apr 12, 2016 at 13:29
• ach.. ok.. we interpreted differently. The countif misled me I suppose. The number of rows that have the string is what should be returned..
– JPV
Apr 12, 2016 at 13:34
• Another thing that could cause misleading is the use of 1 and 0 instead of TRUE or FALSE :) Apr 12, 2016 at 13:40
• Rubén, still not sure: if a 1 needs to be returned for a row that has that string in all indicated columns, then the number of rows with a 1 should be 4 ?
– JPV
Apr 12, 2016 at 13:50
• The label at the left of the cell give us a tip: "No psoriasis on this area" :) Apr 12, 2016 at 13:56

Try

```=IF(
AND(
ArrayFormula(
FILTER(
{Failed!M2:M,Failed!O2:O,Failed!T2:U,Failed!W2:X,Failed!Z2:AC},
LEN(Failed!A2:A)
)
=
"No, I do not have psoriasis on this area"
)
),
1,
0
)
```
• Returns an output of 0, which seems incorrect.
– JPV
Apr 12, 2016 at 13:24
• @JPV I double checked and it works fine. Apr 12, 2016 at 13:27
• Not in the sheet the OP shared (see cell B21) ?
– JPV
Apr 12, 2016 at 13:32
• I made a copy of the OP spreadsheet and after that the original file was changed. Not all the related cells in the current version of the OP spreadsheet are equal to "No, I do not have psoriasis on this area", so the result should be 0, and it is :) Apr 12, 2016 at 13:37
• hey JPV and Ruben, looks like one of you gave me the right formula (cell E21). It's working! Thanks a lot!
– Vee
Apr 12, 2016 at 14:13