Forum Discussion
Solved
Formula Help: Count Blanks
Hello!
So I have a tracking spreadsheet that currently counts how many of an item we report as Yes and No. We also have to account for blanks.
The current formula I am using is : =COUNTBLANK(A2:A80)-11
The reason we are subtracting 11, is that is how many headers we have in between. Is there a simpler formula to track blank entries (and not have to factor in the headers)? We frequently have to add new headers, when new reports are added. We have a separate formula for the Yes and No tracking.
See below for an example, we have 40+ columns that are tied to these headers that track different information, but I only need to track the blank sin a few columns:
| Header - Jan | |
| Yes | |
| Yes | |
| No | |
| Yes | |
| Header - Feb | |
| Yes | |
| No | |
| Header - Feb Blue Team | |
| Yes |
Thank you!
Suppose your headers are in column A and your yes/no values are in column B.
Then you can count the blanks in column B on non-header rows with this formula:
=COUNTIFS(A1:A14,"",B1:B14,"")I.e. count those rows where both A and B are blank.
Suppose your headers are in column A and your yes/no values are in column B.
Then you can count the blanks in column B on non-header rows with this formula:
=COUNTIFS(A1:A14,"",B1:B14,"")I.e. count those rows where both A and B are blank.
- Are all 11 headers at the top of the sheet or are they embedded in the column?
Are there blanks in the header rows which can't be considered 'true' blanks for this calculation?
Can you share a file or mocked up example to help explain a little better?- I added an example in my original post. The headers are embedded throughout the column.Thank you!
