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!