Count Number of rows based upon multiple Criteria
Here is my problem:
I have 6 columns that may contain a number (score), if they all contain a number (score) they will sum into column 7 for a grand total.
In some cases not all of the original 6 columns have a number (score) and I want to count how many rows are missing a score.
I already have formulas for count with score and no score, and could use the difference as the missing score but want the additional formula to validate my counts.
i.e. No Score + missing score + score = total
Frank Smith, PMP
Assistant Director  IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎
Best Answer

Hey @Frank.Smith
One solution of counting your blank cells is to add a Text/Number helper column with this formula
=COUNTIFS([Column1]@row:[Column6]@row, ISBLANK(@cell))
This will count how many blank cells are in each row. You could use a sheet summary field to sum the total in all the rows =SUM([Helper column]:[Helper column]). In both formulas, be sure to rename the columns in the formula above to match your actual column names.
If the columns aren't contiguous, then add the column@cell in one by one to the COUNTIFS function, each having ISBLANK(@cell) as the criteria. ex. =COUNTIFS([Column1]@row, ISBLANK(@cell), [Column2]@row, ISBLANK(@cell), etc)
cheers
Answers

Hey @Frank.Smith
One solution of counting your blank cells is to add a Text/Number helper column with this formula
=COUNTIFS([Column1]@row:[Column6]@row, ISBLANK(@cell))
This will count how many blank cells are in each row. You could use a sheet summary field to sum the total in all the rows =SUM([Helper column]:[Helper column]). In both formulas, be sure to rename the columns in the formula above to match your actual column names.
If the columns aren't contiguous, then add the column@cell in one by one to the COUNTIFS function, each having ISBLANK(@cell) as the criteria. ex. =COUNTIFS([Column1]@row, ISBLANK(@cell), [Column2]@row, ISBLANK(@cell), etc)
cheers

Thanks for the help, solution put me on the right path and gave me some additional data points for reporting.
Frank Smith, PMP
Assistant Director  IT Special Projects Mgr.
Oregon Parks & Recreation Department
If my response helps, please mark it as an accepted answer. 😎
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!