Formula to use to return a value of 1 for several cells that meet a certain crtieria

Question

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.

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"