Filtered tables and running totals

[aiki100]

Hello all -
I am a bit stick here, and could use a little push in the right direction, please:

Column A has numeric data
Column B has a running total of the data in A
(and there are a number of other columns…)


I have a line chart that depicts the curve of Column B, ie, a line chart of the running total. All fine.


Now, in order to run what if scenarios, I changed all the columns to tables, and linked the running total chart to the (now) dynamically expanding column B. I then filtered (some other) column to restrict the results to a certain criteria - this affects the running total.


For those of you in the know, you already see what my issue is:


The chart is using ALL the data points in the running total, and not just the visibly filtered ones.


I need my final row (and each visible row before it) in Column B (running total) to have calculated only those rows that are visible (filtered), and thus have the chart represent the curve of ONLY those visible rows.


I know there is a way to subtotal the last row, but I don't think that will help with the chart, which needs to accurately depict each discrete rows' calculated running total, in order to build the line chart. The chart need to reflect calculations ONLY of each row that is visible via the filter.


Has anyone got an idea on this? Thanks so much for any and all thoughts…

 

[RoryA]

You can use subtotal in each row:

=+SUBTOTAL(109,$A$2:$A2)

in B2 and fill down.

 

[aiki100]

Hi RoryA -

Can't thank you enough for your reply! I wasn't able to get to it till just now, so apologies for the late reply in thanking you.

Weirdly, I tried your formula, and it did indeed work, then it didn't, till I modified B2 to be:


=+SUBTOTAL(109,$A$1:$A2)

I checked this all over and it seems to work, but am curious why it needed to be different, as your version seems cleaner. No matter though, it works and I would have never found it had it not been for you: if you do have any thoughts, I'd like to hear them just from a leaning perspective.

thank you again for your time an thoughts - very much appreciated!

 

[RoryA]

What do you mean exactly by "then it didn't"? What happened?