How to create a group-by sum grouped by a date column?

[Mr. Snrub]

My worksheet has the following structure:

Date           Amount
4/9/2015    28.5810426
4/9/2015    114.3087462
4/9/2015     428.638518
4/23/2015   28.5803476
4/23/2015   114.3097186
4/23/2015   428.6390738
4/24/2015   169.02827915

I want a function that will allow me to create two more columns, one for the date, and other for the sum of the amounts for that date, like so:

Date        Amount
4/9/2015  571.5283068
4/23/2015  571.5283068
4/24/2015  169.02827915

How do I do this?

 

[Eric W]

Easiest way is with a pivot table. Select your range, from the Insert tab click Pivot table. Click OK, then from the PivotTable Fields dialog, drag Date to the Rows box, and Amount to the Values box. Done.

Next best is to create a list of unique values. You can use the Advanced Filter to do that, or there are formulas. Then use the SUMIF function.

Let me know if this works, or if you need additional assistance.

 

[Mr. Snrub]

I want whatever will be the most minimal effect on my workbook's memory. I thought that PivotTables were a real bear on your workbook's memory and speed. Please give more details of the second solution.

 

[Eric W]

Given that your unique values are dates, and therefore numbers, you can use SMALL to generate the unique list.

Excel 2012

	A	B	C	D	E	F
Date	Amount	Date	Total Amount
4/9/2015	4/9/2015
4/9/2015	4/23/2015
4/9/2015	4/24/2015
4/23/2015
4/23/2015
4/23/2015

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]28.58104
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]571.5283
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]114.3087
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]571.5291
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]428.6385
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]169.0283
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"]28.58035
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"]114.3097
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]

[TD="align: right"]428.6391
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"]4/24/2015
[/TD]
[TD="align: right"]169.0283
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2
[/TH]
[TD="align: left"]=IFERROR(SMALL($A$2:$A$8,COUNTIF($A$2:$A$8,"<="&MAX($E$1:E1))+1),"")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]F2
[/TH]
[TD="align: left"]=IF(E2="","",SUMIF($A$2:$A$8,E2,$B$2:$B$8))
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Put the headings in E1:F1, then put the formulas in E2:F2, change the values to match your sheet, then copy down the column as far as needed.