Apr 17 2022 08:56 AM
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?
Apr 17 2022 09:38 AM
SolutionIt 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 <> "") )
Apr 18 2022 04:32 AM - edited Apr 18 2022 04:35 AM
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.
Apr 18 2022 05:08 AM
@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.
Apr 19 2022 02:55 AM
Many thanks for useful extra info.
If it wasn't for guys like you, guys like me would be in despair with Excel.
Apr 17 2022 09:38 AM
SolutionIt 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 <> "") )