2

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?

2
  • 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

2 Answers 2

2

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")
5
  • 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
1

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
 )
6
  • 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
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.