Filter function in Excel when no entries are found

user_13892219 0
Viewed 806

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.

excel-formula
3 Answers

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.

user_105482340

If the Filter is Empty, then it returns the Value "" (since this is the third parameter you have passed it). Your COUNTA then counts that there is 1 value.

Bypassing this is actually slightly difficult: there is no way to pass an empty Array to COUNTA. Even if you pass it an Error Value, it will just count how many Error Values it receives.

Similarly, you can't use COUNTIF, as that doesn't work with Arrays. AGGREGATE won't filter out the Errors, as the Array involves a calculation. That leaves us with SUMPRODUCT. Instead of returning "" when the Filter is Empty, we will return an #NA! error, so that we can count how many non-error entries there are:

=SUMPRODUCT(1-ISERROR(UNIQUE(FILTER(B:B; G:G=A1; NA()))))
user_92909860

You could try this as well.

=IF(IFNA(FILTER(B:B,G:G=A1),0)=0,0,UNIQUE(FILTER(B:B,G:G=A1)))

Given that the filter function returns 1 even when the value is supposed to be 0, we can put in a check for #N/A before executing the function. As @Imran mentioned that when no results are found, the function returns #N/A, we can build a check that takes care of #N/A and convert it to 0 explicitly. Then if it's not 0, we execute the function of interest which in this case is UNIQUE(FILTER(B:B,G:G=A1))

user_192438340
Related