Count Number of rows based upon multiple Criteria

Frank S.
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

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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    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

  • Frank S.
    Frank S. ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!