I am trying to clean my formulas

I favor using FILTER in my formulas. FILTER will return #N/A when it can not find any matches in a filter. And COUNTA includes #N/A errors in its count. So using this table with the following formulas.

=COUNTA(FILTER(A1:A3, A1:A3 = "foo"))
=COUNTA(FILTER(A1:A3, A1:A3 = "bar"))
=COUNTA(FILTER(A1:A3, A1:A3 = "baz"))
=COUNTA(FILTER(A1:A3, A1:A3 = "Gabriel"))
=COUNTA(FILTER(A1:A3, A1:A3 = "bog"))
=COUNTA(FILTER(A1:A3, A1:A3 = "nit"))
=COUNTA(FILTER(A1:A3, A1:A3 = "bug"))

All of the following formulas will return 1. Even if it doesn't find a match! The value will be one because it is counting the #N/A

The only work around I have found is doing something like this

=IF(IFERROR(FILTER(A1:A3, A1:A3 = "bog"), -1) = -1, 0, COUNTA(FILTER(A1:A3, A1:A3 = "bog"))

This more than doubles the length of each formula I use this method on. In Excel I would just use LET but I need to use Google Sheets.

The closest I got to a solution is using COUNTIF

=COUNTIF(FILTER(A1:A3, A1:A3 = "foo"), NA())

This returns the number of #N/As in the list. Which would be 1 but I need something like

=COUNTIF(FILTER(A1:A3, A1:A3 = "foo"), "<>"&NA())

which doesn't work. Oddly enough it does the exact same thing as the formula previous.

Thanks in advance! God bless you all.

CodePudding user response：

You can add an IFNA() function to result in an empty cell, which COUNTA() doesn't count:

=COUNTA(ifna(FILTER(A1:A3, A1:A3 = "foo"),))
=COUNTA(ifna(FILTER(A1:A3, A1:A3 = "bar"),))
=COUNTA(ifna(FILTER(A1:A3, A1:A3 = "baz"),))
=COUNTA(ifna(FILTER(A1:A3, A1:A3 = "Gabriel"),))
=COUNTA(ifna(FILTER(A1:A3, A1:A3 = "bog"),))
=COUNTA(ifna(FILTER(A1:A3, A1:A3 = "nit"),))
=COUNTA(ifna(FILTER(A1:A3, A1:A3 = "bug"),))