Graphing the sum of an autofilter result

[ElPresidente]

Let's say the data is laid out as follows:

Group 1 $20
Group 2 $20
Group 1 $20
Group 3 $20

If I filter the data by Group1, I will get two rows back. I want to
graph all the rows for that group into one data series on the graph.
I'm thinking I could create a temporary sum row, but there has to be
an easier way?

 

[Jon Peltier]

If all you want to plot is the filtered row in one series, plot the whole
table as one series. When you filter, the hidden rows will not plot, because
by default, Excel does not plot hidden cells.

- Jon

 

[ElPresidente]

What I'm trying to avoid is having to create Pivots. Depending on our
data, we may have up to 30 different charts.

 

[ElPresidente]

In my first example, if I filtered by Group 1, I would want the graph
to combine the two rows in a plot showing $40, not two series showing
$20 each. I know this can be accomplished using PivotCharts, but
unless I'm wrong, the chart has to be linked to the pivot table. If I
have 30 different graphs, it would require 30 pivot tables? In Access,
you can tell a graph to plot the Sum of the Data Series as its data
plot. Is there a similar option in Excel.

 

[Jon Peltier]

Terminology: A series is not a single point. My suggestion would result in a
series with two points.

You can either set up a table with your group names in the first column, and
formulas in the second column that added the appropriate values.

Say your data is in A2:B100. In D2 start listing the group names. In E2
enter this formula:

=SUMIF($A$2:$A$100,D2,$B$2:$B$100)

This gives a sum for all of the groups. This table would be similar to the
pivot table.

Is your intention to chart a single point? With nothing to compare it to?
Seems incomplete to me, but here's some clues.

Add a dropdown or listbox from the Forms toolbar. Set its Input Range to the
list started in D2, and set its Cell Link to G2. In H2 enter this formula

=INDEX(D210,$G$2)

Substitute your actual last cell for D10. H2 will show the item selected in
the dropdown or listbox. Copy H2, select I2 and paste. I2 will now contain
the sum for the item selected in the dropdown or listbox. Make your chart
using the cells H2 and I2.

- Jon