CountA formula with Filter Feature

Question

Split from this thread.

Hi Thomas,

Sorry I have an extension to my question earlier.

With the same example, if I was to filter column B by type, will the =CountA formula be effective to only count the unique values in column A that are showing? Or will it count all unique values even when hidden with the filter?

Answer 1

I'm really sorry, Aleisha. I originally pasted column B into column C for some testing and used the data in column C directly while writing formulas. However, when I was creating screenshots for you, I deleted the data in column C but forgot to update the formula. The correct formula should be:

=COUNTA(UNIQUE(FILTER(A2:A7, B2:B7 = B3)))

This means using the FILTER function to select cells in column A that are in the same row as cells in column B with the value "renew." Then, the UNIQUE function is used to remove duplicates, and finally, the COUNTA function counts the number of items.

Since cell C3 was blank, no matter how the filtering was done, the incorrect formula would always return 1. This was my mistake, and I apologize again.

Let's verify together if this formula works correctly.

For example, if I want to filter cells where Type=Renew, theoretically, the yellow cells represent the data filtered out after Type=Renew. 

After applying the filter, we should get the following result:

The formula should remove a duplicate "Doyle," and the result should return 2.

Again, I apologize for providing the incorrect formula earlier and for not getting back to you sooner since yesterday was my day off. I received your email today and wanted to immediately share my progress with you. Thank you for pointing out my mistake; I will make sure to double-check my responses going forward.

Have a nice day!

Thomas