I need to create a distribution plot that sums values

**Calvin Buchanan** · 05-10-2013, 07:13 PM

Hello,

I have a large data set that includes, among other things, location and monetary amount for each entry. For example:

BC 10000
AB 5000
BC 13000
ON 10000
AB 5000
AB 30000
BC 20000

How do I make a graph or other visual aid that sums up monetary amounts for each location? For this small data a bar graph would have 3 pieces of independant data: AB, BC and ON (bins?) and corresponding monetary totals 40000, 43000, 10000 (sum of what's in each bin?).

Any help would be greatly appreciated!

**FDibbins** · 05-10-2013, 08:32 PM

Hi and welcome to the forum

You could probably do this with a pivot chart, but if you are not falimiar with those, try this.

1st create a table with unique locations
assuming your locations are in column A, enter this array formula where you want to create the table...
=IFERROR(INDEX(Sheet1!$A$2:$A$100,MATCH(0,IF(ISBLANK(Sheet1!$A$2:$A$100),"",COUNTIF($A$1:A1,$A$2:$A$100)),0)),"")
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Then use this, copied down to sum the values, assuming your values are in B, and you started the table in D2...
=sumif(D2:D4,D2,B2:B10)

adjust ranges as needed.
You can then create your chart based on that table

**Calvin Buchanan** · 05-13-2013, 01:06 PM

Thanks for the reply!

Maybe I'm just not understanding your code but that doesn't seem to be doing what I want. My table has 650 entries with locations from A2:A651 and monetary amounts from B2:B651. I changed the array formula to:

=IFERROR(INDEX(Sheet1!$A$2:$A$651,MATCH(0,IF(ISBLANK(Sheet1!$A$2:$A$651),"",COUNTIF($A$1:A1,$A$2:$A$651)),0)),"")

and then tried to implement it from D2:D651. Every cell was just filled with BC, one of the locations. Not really sure if I am implementing it wrong or if there is some misunderstanding.

Any further help would be much appreciated!

**FDibbins** · 05-13-2013, 03:10 PM

OK lets try it this way instead...

I put your data from post #1 in A2:B8. Then in D2, copied down 5-6 rows...
=IFERROR(INDEX($A$2:$A$8,MATCH(0,INDEX(COUNTIF($D$1:D1,$A$2:$A$8),0,0),0)),"")
and in E2, copied down...
=IF(D2="","",SUMIF($A$2:$A$8,D2,$B$2:$B$8))