Aren't Stacked Column charts supposed to stack values for a total per column?

stuckfly

New Member
Joined
Mar 10, 2009
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
I have a table with two columns: one for transaction dates and the other for amounts.
I want a chart that will stack each month's transactions on top of each other to show monthly totals.
Seems like this would be simple, but I'm not a seasoned Excel user.
The closest I can get shows transactions overlapped per month, so as the column for each month shows the maximum transaction as chart column height.
Example:

For December 2019 there is one transaction for $102, for January there are two: $46 and $102.
Both chart columns "stack" to $102. Shouldn't the chart column for January be taller to show $148?
That's what I need…

Any way to do that?
I wouldn't mind a sum label at the top of each chart column, too.

Tried to add an image, but even the smallest cropped screen shot is too large of a file to upload to this board. It's 269KB and barely legible at that size.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
I have no issue with it stacking correctly. How have you laid out the data (this seems like your issue as you have stated "I have a table with two columns: one for transaction dates and the other for amounts.", which if I am reading it correctly is not a good layout for a chart)?

Btw you can't upload files and 269Kb is not a large file ;)

1589875481915.png



1589875617708.png
 
Last edited:

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,430
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If you can't change your data layout then the best that I can suggest is creating a new series using formula then creating the chart from there i.e.
Book1
ABCDEFGHIJKLMNOP
2December102unique 123456789101112
3January102December102           
4January24January10224          
Sheet1
Cell Formulas
RangeFormula
D3:D4D3=IFERROR(INDEX($A$2:$A$10, MATCH(0, COUNTIF($D$2:D2, $A$2:$A$10), 0)),"")
E3:P4E3=IFERROR(INDEX($B$2:$B$10, SMALL(IF($D3=$A$2:$A$10, ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1, ""), E$2)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.


1589878067732.png
 

stuckfly

New Member
Joined
Mar 10, 2009
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
Got it!
Took me awhile.
I noticed that your examples (thank you for saying I cannot upload images:) show months as categories and not dates or date values, and I eventually paid more attention to your comment about "if I am reading it correctly is not a good layout for a chart."
Makes no sense to show individual transactions stacked up; it's the totals that really matter.
I used another sheet with one row per month and a SUMIF formula in the second column that refers to named ranges in the first sheet to compute totals per month, and now I have the simple chart I want. I abandoned the stacked column in favor of a line chart so I could add a trendline.
In future, if I want to do the stacked transactions per month chart I had envisioned, you've given me the key to do that by setting up each month as a category with each transaction record belonging to a one of them. And I'm betting I could even add a total for each month then, as a category label series.
Many thanks, Mark858!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,279
Messages
5,546,943
Members
410,764
Latest member
Dedeke
Top