COUNTIF or COUNTIFS to sum blank fields

Options

I have many columns and I need to sum the number of blanks within a row. There are a few columns interspersed that are hidden helper columns that should not be included in the count. I have not been able to get a formula to work when trying to account (and not count) for the helper columns (in this example column 11 and 13).

What I tried: =COUNTIFS([Column 1]:[Column 10], ISBLANK(@cell), [Column 12]:[Column 12], ISBLANK(@cell), [Column 14]:[Column 20], ISBLANK(@cell))

I also tried converting to a column formula with @row after the ranges, but continue to get incorrect argument set.

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Chris Walker

    You have a choice in the formula. You could move your hidden helper columns out of the middle of your range to make a contiguous range (if they are hidden perhaps it doesn't matter where they are?). If this is not possible, you will separate the ranges into different COUNTIFS and add them together. =COUNTIFS(range1, isblank(@cell))+COUNTIFS(range 2, isblank(@cell))+etc

    Also, you mentioned you wanted to sum the blanks within a row yet you are counting all the cells in the columns. Is this the intent, or did you actually want to get the count row by row?

    Will either of these options work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Chris Walker

    You have a choice in the formula. You could move your hidden helper columns out of the middle of your range to make a contiguous range (if they are hidden perhaps it doesn't matter where they are?). If this is not possible, you will separate the ranges into different COUNTIFS and add them together. =COUNTIFS(range1, isblank(@cell))+COUNTIFS(range 2, isblank(@cell))+etc

    Also, you mentioned you wanted to sum the blanks within a row yet you are counting all the cells in the columns. Is this the intent, or did you actually want to get the count row by row?

    Will either of these options work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!