Show number of records filtered in pivot table

[Costas]

Hi,

I've created a pivot table from an excel table which shows the sales per customer on a monthly basis so my pivot shows Customer code, Customer name, a sales column for each month and a grand total. I've sorted my pivot in descending order based on the grand total amount and filtered the top 15 customers. This pivot will be sent to all our sales managers and they will be able to filter the number of customers they want to see.

I want to enter a formula in a cell above my pivot table that shows the number of records showing and total records, something like... ="Number of selected customers " & subtotal(3,"Customer No") & " of total customers" & counta("Customer No"). My problem is that I can't get my referencing right for either of the two functions. Any ideas guys?

Thanks
Costas

 

[Somendra Misra]

Hi Costas,

In the absense of a sample file, I had tried to create one. Just see if this what you are looking after. Yellow cell has the formula,

Regards,

 

[Costas]

Hi Costas,

In the absense of a sample file, I had tried to create one. Just see if this what you are looking after. Yellow cell has the formula,

Regards,

Hi Somendra,

Thanks for your reply and sorry for not providing you with a sample data file.

I now attach a sample of what I'm trying to do which includes a pivot table and an excel table. The problem is adjusting subtotal function to reflect the range changes after filtering my data. Is it possible to do a search to find the row numbers of "Customer No." and "Grand Total" in column B and then calculate the range as Grand Total row - Customer No. row -1?

Thanks
Costas

 

[NARAYANK991]

Hi Costas ,

See if this is OK in M2 :

=COUNTA($B$7:INDEX($B:$B,MATCH("Grand Total",$B:$B,0)))-1

Narayan

 

[Costas]

Yes, this works! Many thanks Narayan