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

Hi AleishaB,

Thanks for your post in Microsoft Community.

Actually, using the Filter feature in Excel to filter cells is only visual. The cells are still present on the worksheet. You may have noticed that there are gaps between cells A1 and A3. This indicates that cell A2 has not disappeared; it's just hidden from view.

Formulas work based on the actual cells, not the displayed ones. Therefore, the previous formula does not work perfectly in this case.

However, you might consider using the FILTER function, which can achieve similar functionality to the filter tool.

For example, if I want to count the names in column A when Type equals "renew", using Excel's Filter feature results in this:

Using this formula:

=COUNTA(UNIQUE(FILTER(A2:A5, B2:B5 = C3)))

The name "Doyle" after being filtered and deduplicated is counted as only one instance, and the updated formula works correctly.

I hope the information above is helpful to you!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 2

Hi,

In cell A8, enter this formula

=COUNTA(UNIQUE(FILTER(A2:A5,BYROW(A2:A5,LAMBDA(r,SUBTOTAL(3,r))))))

Hope this helps.

Answer 3

Hello Thomas,

Yes, exactly. What you have explained is exactly what I would like to achieve. However, when I have used this formula from the example in my real data set, it returns only 1, however due to my data having 17169 rows of data, I know the result should be more than 1.

Can you please explain what C3 in your example represents as in the example I am unsure how to mirror this in my real data set.

I am trying to count how many different names are new and how many different names are renew.

Thanks,

Aleisha

Answer 4

Hello Ashish,

When I have used this formula from the example in my real data set, it returns only 1, however due to my data having 17169 rows of data, I know the result should be more than 1.

I am trying to count how many different names are new and how many different names are renew.

Thanks,

Aleisha

Answer 5

Hi,

I know my formula is correct. Cannot say what mistake you are committing.