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
 Smartsheet Customer Resources
 61.2K Get Help
 321 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 277 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!