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?

Improve this question
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?
    – Rubén ♦
    Apr 12 '16 at 13:32
  • yes, that's right.
    – Vee
    Apr 12 '16 at 14:10
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")
Improve this answer
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.
    – Rubén ♦
    Apr 12 '16 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 '16 at 13:34
  • Another thing that could cause misleading is the use of 1 and 0 instead of TRUE or FALSE :)
    – Rubén ♦
    Apr 12 '16 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 '16 at 13:50
  • The label at the left of the cell give us a tip: "No psoriasis on this area" :)
    – Rubén ♦
    Apr 12 '16 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
 )
Improve this answer
6
  • Returns an output of 0, which seems incorrect.
    – JPV
    Apr 12 '16 at 13:24
  • @JPV I double checked and it works fine.
    – Rubén ♦
    Apr 12 '16 at 13:27
  • Not in the sheet the OP shared (see cell B21) ?
    – JPV
    Apr 12 '16 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 :)
    – Rubén ♦
    Apr 12 '16 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 '16 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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