Excel - Chart that sums the values in multiple rows for each series

Suppose I have a spread sheet that looks something like this...

Excel Data

Now, I'd like to create a column chart that has 3 series, one for each country. Then, I want series for each category, but I want to plot the total, not each individual order total. So, something like this (excuse the horrible artwork)...

Excel Chart

The data label placement isn't all the important, the key is that for each Category (Bikes and Clothes) I chart the total for each country, not individual values from the "Order Total" column.

Is this possible? Is it possible to do the same idea, but to switch Country and Category around?

Chaulky

Posted 2011-01-05T23:01:05.157

Reputation: 727

// , Thanks for the question! I'm still learning the use cases for Pivot Tables, so I think it's relevant to the tags you used. Maybe consider adding the tag "pivot-tables"? – Nathan Basanese – 2016-02-08T22:50:13.747

Answers

Pivot tables and pivot charts are perfect for this purpose. They'll let you summarise your data into a table and chart and to easily reorganise both of them.

Mike Fitzpatrick

Posted 2011-01-05T23:01:05.157

Reputation: 15 062

You can make a regular chart from a pivot table. Start by selecting a blank cell that doesn't touch any data and insert the chart, then use the Edit Data dialog to add the series one at a time. See Making Regular Charts from Pivot Tables,

– Jon Peltier – 2016-06-07T12:05:22.473

Yes, but i'd really like a regular chart unfortunately. – Chaulky – 2011-01-06T00:55:53.853

1@Chaulky: I don't think it can be done any other way. If there is an alterative you'll probably find it to be so complicated as to outweigh any disadvantages you perceive pivot charts having over normal charts. Hopefully some can enlighten us both. :) – Mike Fitzpatrick – 2011-01-06T01:14:40.740

You may want an array formula (I found it by looking for "double sumif()") which essentially lets you specify more than one criteria for the IF part.

See this page for more information: http://www.cpearson.com/excel/ArrayFormulas.aspx

JYelton

Posted 2011-01-05T23:01:05.157

Reputation: 2 848