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.


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 '16 at 15:43
  • @Vee I updated the information for you – Aurielle Perlmann Apr 9 '16 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 '16 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 '16 at 2:46
  • I don't see the count formula but im in the sheet now - on the chat – Aurielle Perlmann Apr 11 '16 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.