COUNTIF or COUNTIFS to sum blank fields

Chris Walker

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.

Kelly Moore

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

Kelly Moore

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