AANTALARG

[Theo Dam]

I Have a spreadsheet where I count all cells that arn't empty; The formula looks so:

=AANTALARG(c2:C5000

When I accidentally enter a space in one of the cells, then the result is +1 (one too much)

When I try to use a filter on that column, my only selectable cells are either "V"or empty cells, but not the "space"cells.

When I delete the Spave, that the result turns right again.

[#2]

Hi Theo,

That's correct behavior, cell with the space is not empty. If exclude both you may use

=SUMPRODUCT(--(TRIM(C2:C5000)<>""))

Not sure what are the names of above functions in your locale.

[#3 · Accepted solution]

AANTALARG is COUNTA in English. And this is expected behaviour. It is odd that Excel's filtering treats cells with just a space in them as blanks though.

[#4]

That is correct, I expect the filter to show me the blank entries and not to treat them as empty.
There is no possibility to find entries that only contain a blank.

[#5]

Advance filter could filter the blanks only. How it works is for example here https://www.techrepublic.com/blog/microsoft-office/how-to-find-blank-records-using-excels-advanced-f...

[#6]

Thank all you. I replaced my formula with the following one that solved the problem:
=SOMPRODUCT(--(SPATIES.WISSEN(C2:C26950)<>""))
(spaties.wissen) is dutch for TRIM.