asked on

Getting occurrence within an excel column even when the column is filtered

Hi,

We use this formula to get the occurrence of items within a column ("=SUM(IF(FREQUENCY(MATCH(B10:B64781,B10:B64781,0),MATCH(B10:B64781,B10:B64781,0))>0,1))"). However, we need to place filters in the columns and want to reflect the occurrence as the items being filtered. We took a look at "=SUBTOTAL" but can't get it work as we want it.

Is there a way to this?

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jana

ASKER

Gave me a "Circular Reference Warning".

I have included an excel with a small data. I placed the formula I usually use without filter = the result is 8 unique register (but since I can't set filter, it stays 8).
EEfilter.xlsx

Subodh Tiwari (Neeraj)

You were getting the circular reference warning because you placed the formula cell (B4) itself in the formula.

Please refer to the attached.
EEfilter.xlsx

jana

ASKER

Thanx worked!

However, when I copied the formula over to my working excel (the same cell B4) but says says "#VALUE!". Noticed in your excel that the formula is in brackets but when I go into it,the brackets disappear - do i have to do something to activate the formula?

jana

ASKER

Ok, I had to ctrl-shift.

Ok thanx

Subodh Tiwari (Neeraj)

That's right. Since it is an Array Formula, you need to confirm it with Ctrl+Shift+Enter instead of Enter alone.

jana

ASKER

Thank you very much!

May I bother you for a bit of explanation of the formula?

Subodh Tiwari (Neeraj)

In SUBTOTAL(3, 3 is for COUNTA so the Subtotal formula is finding the visible cells with some content in them and so the Frequency output is filtered based on the Subtotal criteria as well and in the end you get the unique count from the visible cells.

jana

ASKER

Thanx!

Subodh Tiwari (Neeraj)

You're welcome!