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
Check out the Formula Handbook template!