COUNTIF or COUNTIFS to sum blank fields
I have many columns and I need to sum the number of blanks within a row. There are a few columns interspersed that are hidden helper columns that should not be included in the count. I have not been able to get a formula to work when trying to account (and not count) for the helper columns (in this example column 11 and 13).
What I tried: =COUNTIFS([Column 1]:[Column 10], ISBLANK(@cell), [Column 12]:[Column 12], ISBLANK(@cell), [Column 14]:[Column 20], ISBLANK(@cell))
I also tried converting to a column formula with @row after the ranges, but continue to get incorrect argument set.
Best Answer
-
Hey @Chris Walker
You have a choice in the formula. You could move your hidden helper columns out of the middle of your range to make a contiguous range (if they are hidden perhaps it doesn't matter where they are?). If this is not possible, you will separate the ranges into different COUNTIFS and add them together. =COUNTIFS(range1, isblank(@cell))+COUNTIFS(range 2, isblank(@cell))+etc
Also, you mentioned you wanted to sum the blanks within a row yet you are counting all the cells in the columns. Is this the intent, or did you actually want to get the count row by row?
Will either of these options work for you?
Kelly
Answers
-
Hey @Chris Walker
You have a choice in the formula. You could move your hidden helper columns out of the middle of your range to make a contiguous range (if they are hidden perhaps it doesn't matter where they are?). If this is not possible, you will separate the ranges into different COUNTIFS and add them together. =COUNTIFS(range1, isblank(@cell))+COUNTIFS(range 2, isblank(@cell))+etc
Also, you mentioned you wanted to sum the blanks within a row yet you are counting all the cells in the columns. Is this the intent, or did you actually want to get the count row by row?
Will either of these options work for you?
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!