Pie chart- need to combine values

[dev988]

Hi!
I am trying to set up a pie chart using 2 columns of data- one is a list of money amounts and the other is a list of the state it came from. I need a pie chart that gives me a break down of money by state. When I set it up and put the 2 columns in as seperate values it treats each line as something by itself, meaning new york is there 50 times as 50 seperate colors of the pie- I need it to combine all "NY" into one color. Anyone know how i do that??
Thanks!!!

[Bryan Hessey]

Hi!
I am trying to set up a pie chart using 2 columns of data- one is a list of money amounts and the other is a list of the state it came from. I need a pie chart that gives me a break down of money by state. When I set it up and put the 2 columns in as seperate values it treats each line as something by itself, meaning new york is there 50 times as 50 seperate colors of the pie- I need it to combine all "NY" into one color. Anyone know how i do that??
Thanks!!!

Assuming that your amounts are in column A, and the states in column B, create a unique list of states by select the states column, then Data, Filter, Advanced Filter, Copy to another location, Unique records only, to New location C1

In D1 put

=SUMPRODUCT(--(B$1:B$10=C1)*(A$1:A$10))

and formula fill that down the unique list of states, then select C1:D999 (the end of your data) and Insert Chart, Pie

The pie chart should almost be correct as is, adjust labels and legends etc.

Hope this helps
---

[dev988]

It worked! thank you so much!!

[Bryan Hessey]

It worked! thank you so much!!

Good to see that it worked for you, and thanks for the response.

---