Dynamic sum formula

[FlexicareTommy]

I'm trying to create a dynamic sum formula off of a table of data which is then used in pivot tables and pivot charts. What I would like it to do, is show me the summation of values AFTER a filter has been applied. Everything I've tried returns a sum of the entire table (includes hidden cells). Perhaps I'm searching with the wrong query terms (this not called a dynamic sum formula?), but I can't seem to find anything to help.

I'll describe what I'm hoping to achieve, and maybe someone can shed some light on the topic. I very well might be approaching this from the wrong angle.

Using slicers and pivot charts, I need my account managers to be able to look at their territories quarterly performance vs. a "fair share" of company performance during that quarter. I.e. they would select the slicer for their region, and dates (ex april, may, june) and the pivot chart would show territory performance during that time, with a second column chart series which is company total from april/may/june (my dynamic sum) divided by the 8 territories...thus giving me what should be "fair share" during that quarter. Each territory has equal Opportunity, so this would allow them to see who is over-performing and underperforming in their territories. Can anyone help me? Thanks!!

 

[Jonmo1]

You can use the SUBTOTAL Function to ignore rows hidden via Filter

=SUBTOTAL(109,A:A)


Hope that helps.

 

[FlexicareTommy]

You can use the SUBTOTAL Function to ignore rows hidden via Filter

=SUBTOTAL(109,A:A)


Hope that helps.

SUPER Helpful Thank you! I didn't know that function existed!