Can I use OR function within a COUNTIFS?
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.
Answers

@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.

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

@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)) ))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!