Filter function in Excel when no entries are found

Question

I try to use a combination of UNIQUE() and FILTER() function in Excel O365 Pro Plus. I have a list of countries in column B, and a list of different project stages in column G. I would like to filter out the unique number of countries from column B with the number "1" in column G (The reason I've written A1 in the formula is because I have a "1" in cell A1), and if there are no "1:s", then I want the function to return a "0" or something indicating "No entries found".

When I use this function:

=COUNTA(UNIQUE(FILTER(B:B;G:G=A1;"")))      

I get the correct values when I have at least one country in column B with a "1" in column G, but it doesn't return a "0" when there are no countries matching the criterion, it returns a "1". I did some research and was thinking the "" that I've written in the end of the formula should fix this but it doesn't work.

Anyone who knows what's wrong?

I'm new here so apologies in advance if I could've written the question clearer. Thanks a lot in advance.

Answer 1

This part:

=UNIQUE(FILTER(B:B,G:G=A1))

Shows an error if you don't have any entries:

#CALC!

So COUNTA calculates all the Cells that are not empty. Cell with an error isn't empty so you will never have 0.

Try to use something like this:

=SUMPRODUCT(--NOT(ISERR((UNIQUE(FILTER(B:B,G:G=A1))))))

We're checking if it's not an error and count it only in that case.