• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count unique value which require interact with filter

K

KIM Chanthan

Member
Hi,

I want to count how many unique value from a columns.
I think of using =COUNTA(UNIQUE(B3:B10)). It works well. However, it doesn't reflect with filter.
So i try with =SUBTOTAL(2,UNIQUE(B3:B10)) and it throw error.
Can you help to share alternative approach without creating other helper column?

Thanks,
 
With Excel for MS365 or Excel for the web, you could try using SUBTOTAL with BYROW as the include argument of the FILTER function. For example:

Code: 
=IFERROR(ROWS(UNIQUE(FILTER(B3:B10,BYROW(B3:B10,LAMBDA(r,SUBTOTAL(3,r)))))),0)

If the BYROW function is not available in your version of Excel, you could simply use the FILTER function (instead of manual filters) with the same criteria that is currently applied to the manual filters, whatever that may be. For more information, please see: https://exceljet.net/functions/filter-function
 
You must log in or register to reply here.
Back
Top