Count Number of rows based upon multiple Criteria

Frank S.

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

Kelly Moore

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

Kelly Moore

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

Frank S.

Thanks for the help, solution put me on the right path and gave me some additional data points for reporting.