Forum Discussion
COUNTA() not working as desired on calculated columns
COUNTA() does not work as desired on a column containing apparently zero-length blank cells that are the result of a calculation (for example where [...=""]).
Is there a practical work-around for this?
It depends on what you'd like to calculate. COUNTA() work correctly. If you add any text to the cell, includes empty string, it excluded from calculation.
As variant that could be
=SUMPRODUCT(--(range <> "") )
4 Replies
It depends on what you'd like to calculate. COUNTA() work correctly. If you add any text to the cell, includes empty string, it excluded from calculation.
As variant that could be
=SUMPRODUCT(--(range <> "") )Many thanks for this.
As an octogenarian excel tyro, I had never even heard of SUMPRODUCT function, but while I still don't really understand how it works, it solves my problem.
I was trying to count how many cells (excluding those that appear empty) contained a calculated text result, but I didn't realise that a zero-length cell whose result is calculated as "" is not seen by COUNTA() as an empty cell.Heapug , you are welcome
SUMPRODUCT in this concrete case first creates an array where each element of the range is compared with empty string. Blanks are also calculated. That will be array of TRUE and FALSE. Double dash in front converts them to 1 and 0 accordingly. Finally we sum it. Result is actually number of elements for which condition is met.
COUNTsome() functions have specific.
COUNTA() excludes empty string from calculations, but COUNTBLANK() counts cells with empty strings as blank cells.
COUNIFS(range, "<>") also doesn't count cells with empty strings.
