Can I use OR function within a COUNTIFS?

Susan Vieira

Hello,

I have a document with 7 questions in 7 different columns. I am trying to count how many rows have any one of the 7 questions blank and I am struggling.

If I do a COUNTIF like this:

=COUNTIF({Q1 Answer}, ISBLANK(@cell), {Q2 Answer}, ISBLANK(@cell), {Q3 Answer}, ISBLANK(@cell), {Q4 Answer}, ISBLANK(@cell), {Q5 Answer}, ISBLANK(@cell), {Q6 Answer}, ISBLANK(@cell), {SQ7 Answer}, ISBLANK(@cell))

it will only count the row if all of the 7 questions are blank. I don't know how to formulate my formula, to count the row, if any of the 7 columns are blank. Is there any way to incorporate an OR in the "range" portion of my count if? I have not had much luck. Or is there another way to do this altogether?

Appreciate any help.

sharkasits

@Susan Vieira You can use an OR in a COUNTIF function, but not for what you are trying to do, you can use it to test the same range with different criteria. For what you're trying to do, it's probably easiest to have a helper column in the source sheet, either a check box, or a count of the number of blank fields.

For count of blank fields:

=if(ISBLANK([Q1 Answer]@row),1,0)+if(ISBLANK([Q2 Answer]@row),1,0)+if(ISBLANK([Q3 Answer]@row),1,0)+if(ISBLANK([Q4 Answer]@row),1,0)+if(ISBLANK([Q5 Answer]@row),1,0)+if(ISBLANK([Q6 Answer]@row),1,0)+if(ISBLANK([Q7 Answer]@row),1,0)

Then you can do a countif that helper column is greater than 0.

Susan Vieira

@sharkasits

Thank you. I was afraid of that. I am trying to avoid a helper column if I could but if that is the only way to go, then so be it.

Susan

sharkasits

@Susan Vieira You can do that if you look for the opposite (where they are all filled in) with a count(collect()), and subtract that from a count of all lines.

=Count({<<a field that's not blank>>}) - Count(collect({<<a field that's not blank>>}, {Q1 Answer}, NOT(ISBLANK(@cell)), {Q2 Answer}, NOT(ISBLANK(@cell)), {Q3 Answer}, NOT(ISBLANK(@cell)), {Q4 Answer}, NOT(ISBLANK(@cell)), {Q5 Answer}, NOT(ISBLANK(@cell)), {Q6 Answer}, NOT(ISBLANK(@cell)), {SQ7 Answer}, NOT(ISBLANK(@cell)) ))