Log In
Register
Home
February 9, 2022
Offline
Can anyone explain why COUNTA is counting apparently blank cells?
As shown in the attached sample workbook, I had case numbers in cells. Each cell frequently has multiple "case numbers" separated by semicolons, but sometimes the names also had extra spaces (usually after the text, sometimes within it). (see Original Data table in 1st tab). I had to fix this because I need to use a Case Matter Lookup table (also in 1st tab) to find "matter names" from "case numbers," but any extra spaces stop MATCH, VLOOKUP, etc. from working. Finally, I wanted to use COUNTA to count the number of matter names in each row.
My solution was to:
1) split the column by delimiter in Power Query (see Split_Case_Nos_at_Delimiter table in 2nd tab);
2) use TRIM to remove the extra spaces (see Split_Case_Nos_Trimmed table in 3rd tab). This also worked fine - VLOOKUP returned every matching matter name from the lookup table (subtable 1 on 3rd tab). I got rid of "N/A" values by nesting VLOOKUP within IFERROR to leave a "" (subtable 2 on 3rd tab).
But I got stuck on the 3rd step (counting the number of matter names in a row) because COUNTA kept counting every cell in the row, including the ones that looked totally blank to me. This was true whether the cell contains a formula returning a blank ("") value (as in subtable 2 of tab 3) or if I pasted value (as in subtable 3 of tab 3) to get rid of the formula.
The only solutions I found were to either 1) select the "blank" cells and use DEL or Clear All or 2) use a unique text string in my IFERROR function that I could find/replace at one go. For the life of me, though, I can't understand WHY CountA is counting what appear to be blank cells. Does anyone have an explanation for this? Or a more elegant solution to the problem than subbing in a text string and then replacing it with nothing?
See Answer
October 5, 2010
Offline
Hi Cullen,
The result of your IFERROR when there is an error is "" , which is an empty string, and that isn't the same as a blank cell.
If you use COUNTA on a range that you've done nothing with, it'll count the empty cells as you'd expect. If you then enter ="" into a cell in that same range, you'll see that COUNTA result changes.
To overcome your issue you could try
=COUNTA(B37:J37)-COUNTBLANK(B37:J37)
or
=SUMPRODUCT(--(LEN(B37:J37)>0))
Regarding the use of COUNTBLANK in the first formula. COUNTA says it will count the number of cells in a range that are not empty. COUNTBLANK says that it will count the number of empty cells in a specified range. So COUNTA and COUNTBLANK treat cells containing "" differently. This is inconsistent behaviour, but once you learn this, you can work with it. Really, the functions should be changed so that they both treat data in the same way.
Regards
Phil
Answers Post
February 9, 2022
Offline
I figured it was something like that, Phil: I didn't appreciate the difference between an empty string and a blank cell. Thanks for the quick reply and the useful workaround!
Best,
Cullen
Trusted Members
Moderators
November 1, 2018
Offline
Just for info, you could also use something like:
=COUNTIF(B54:J54,"?*")
to only count cells with at least one character in them.
The following users say thank you to Velouria for this useful post:
Philip Treacy
1 Guest(s)
