• 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 results once slicers activated

John Gibbons

New Member
Good day Chandoo family
The attached file contains a table with two slicers. Problem: How many items (count) based on slicer selection(s)?
Using the CountA function provides a number; however, the number doesn't change based on the slicer selection(s). If possible, I would like to avoid using a pivot table or VBA. I would appreciate any assistance on this problem.
Thank you very much.
 

Attachments

Peter Bartholomew

Well-Known Member
I know it is possible to do better than this, but I resorted to introducing a helper column "S?" to indicate whether a record has been selected or not. The formula it contains is
= SUBTOTAL(3,[@[Document number]])
This is 1 on visible rows and 0 on filtered rows so the number of records showing is given by
= SUM(Table_owssvr[S?])

Edit. AGGREGATE seems to work.
= AGGREGATE( 3, 5, Table_owssvr[Document number] )
 
Last edited:
Top