Here's the spreadsheet I need help with: https://docs.google.com/spreadsheets/d/1DGYgprXe7V7WH2HII0hvQDWXl1SaG_a4MUZ5HDGhSq8/edit#gid=258610801

I basically need cell B21 in the "Report" worksheet to return a value of "1" if columns M, O, T, U, W, X, Y, Z, AA, AB, AC have the answer "No, I do not have psoriasis on this area" in the "Failed" worksheet. I also need it to return a value of "0" if even one of the columns do not have the specific answer ("No, I do not have psoriasis on this area") I am looking for. Any idea/suggestion will be much appreciated.

1 Answer 1


You should be able to do this with a COUNTIF formula:

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

I made an update to make sure it only includes the columns you specified. If you want to try it also on the other sheet you created called copy of Failed then you need to you modify your formula to:

=IF(COUNTIF(ARRAYFORMULA({'copy of Failed'!M2,'copy of Failed'!O2,'copy of Failed'!T2:U2,'copy of Failed'!W2:AC2}),"No, I do not have psoriasis on this area")=11,1,0)

The logic behind count if, is counting the number of times the exact phrase "No, I do not have psoriasis on this area", but since you have that phrase sometimes listed in other fields that you dont care to add up, the arrayformula basically filters to only count on the columns you care about.

Since you said if even one of those doesn't have the phrase, you want a 0, so based on your criteria, all 11 columns should have "No, I do not have psoriasis on this area"

  • Hi Aurielle, thanks for your response. I tried the formula but it didn't work (see spreadsheet). Could you also explain the logic behind the formula so I could understand it better? Thanks in advance.
    – Vee
    Apr 9, 2016 at 15:43
  • @Vee I updated the information for you Apr 9, 2016 at 20:42
  • Thanks Aurielle! Just a follow up question, I realized that the formula works only for row 2. What if I want to apply the formula to the entire 'Failed' worksheet?
    – Vee
    Apr 11, 2016 at 2:32
  • I just tried to add 'count' at the beginning of the formula to achieve what I wanted (Please see the spreadsheet). Is this formula correct?
    – Vee
    Apr 11, 2016 at 2:46
  • I don't see the count formula but im in the sheet now - on the chat Apr 11, 2016 at 3:39

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.