Figuring out the number of non-blank cells in a range is not entirely straightforward. The COUNTBLANK function returns the number of blank cells in a range, but what if you want to count the number of non-blank cells in the same range?
One way is to use the COUNTA function:=COUNTA(B1:B13). The problem with this formula is that it doesn’t return the complementary value to what COUNTBLANK returns. In other words, the result of COUNTA added to the result of COUNTBLANK doesn’t equal the total number of cells in the original range. The reason for this is that both COUNTBLANK and COUNTA treat formulas differently.
COUNTBLANK includes, as blank, formulas that return a blank value. COUNTA does not consider such cells blank (even though a blank is returned), so it includes them in its count. If you consider non-blank cells to be those that are not returned by COUNTBLANK, you will need to use this formula:
=(ROWS(B1:B13)*COLUMNS(B1:B13))-COUNTBLANK(B1:B13)
This subtracts the COUNTBLANK result from the total number of cells in the same range.
This was awesome…..I have been racking my brain trying to figure this out. I have a column of information that is a formula that references another sheet, and the COUNTA formula includes that “hidden” data as a value.
Merci!!!