sum based on date

[hitch_hiker]

I have column"A" holds sequential dates in random instances, (might be any number 1 through to 30 or even more), against each date is a value in column"B", which I need to sum. e.g. sum all for 1 july , sum all for 2 july etc. I really don't want to go through 365 filter for date then sum. Is there an easy or standard way of doing this. Note, I might need to do this across several years so the instances could easily get out of hand.

report	date	total sales
2000​	1/07/2018​	854.25​
newcastle	1/07/2018​	351.00​
gosford	1/07/2018​	557.00​	1,762.25​	=sum(c4:c6)
2001​	2/07/2018​	254.00​
inv2529	2/07/2018​	587.79​
inv2530	2/07/2018​	67.17​
inv2531	2/07/2018​	67.17​
inv2562	2/07/2018​	545.79​
inv2566	2/07/2018​	269.50​
inv2576	2/07/2018​	509.52​	2300.94​	=sum(c7:c13)
2002​	3/07/2018​	132.04​
inv2538	3/07/2018​	305.14​	437.18​	=sum(c14:c15)
2003​	4/07/2018​	215.44​
inv2569	4/07/2018​	112.00​
2004​	5/07/2018​	259.85​
inv2546	5/07/2018​	167.93​
inv2557	5/07/2018​	276.52​
inv2553	5/07/2018​	294.00​
2005​	6/07/2018​	290.75​
inv2550	6/07/2018​	374.71​
inv2551	6/07/2018​	191.91​
inv2588	6/07/2018​	454.86​

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F
2	report	date	total sales
3
4	2000	7/1/2018	854.25
5	newcastle	7/1/2018	351
6	gosford	7/1/2018	557	1,762.25	1762.25
7	2001	7/2/2018	254
8	inv2529	7/2/2018	587.79
9	inv2530	7/2/2018	67.17
10	inv2531	7/2/2018	67.17
11	inv2562	7/2/2018	545.79
12	inv2566	7/2/2018	269.5
13	inv2576	7/2/2018	509.52	2300.94	2300.94
14	2002	7/3/2018	132.04
15	inv2538	7/3/2018	305.14	437.18	437.18
16	2003	7/4/2018	215.44
17	inv2569	7/4/2018	112		327.44
18	2004	7/5/2018	259.85
19	inv2546	7/5/2018	167.93
20	inv2557	7/5/2018	276.52
21	inv2553	7/5/2018	294		998.3
22	2005	7/6/2018	290.75
23	inv2550	7/6/2018	374.71
24	inv2551	7/6/2018	191.91
25	inv2588	7/6/2018	454.86		1312.23
26
Sheet3

Cell Formulas
Range	Formula
E4:E25	E4	=IF(B4<>B5,SUMIFS($C$4:$C$25,$B$4:$B$25,B4),"")

 

[hitch_hiker]

Thanks Maabadi,
so I need to change the range to suit the sheet (952 rows), does that mean sumifs($c$4:$c$952, $b$4 ....... >>>> $b$952) ?

 

[maabadi]

Yes Changed Row Numbers & Column Letter Based of Data Range then Drag formula Down.
if your first row data is 4 then try this:

IF(B4<>B5,SUMIFS($C$4:$C$952,$B$4:$B$952,B4),"")

Otherwise change 4 to first row number & 5 to Second Row number

 

[maabadi]

You're Welcome & Thanks for Follow-up.

 

[Dave Patton]

You can try Excel's Subtotal; it will prepare a summary for you
1 is Grand Total
2 by each change
3 original detail and subtotal
I show the sum by change - 2
Select the Range
Use Data Outline Subtotal
at each change of date
sum
You do not enter the formulas.

N.B. You can post an extract of your sheet with the forum's tool XL2BB.

report	date	total sales
	1-Jul-18 Total	1762.25
	2-Jul-18 Total	2300.94
	3-Jul-18 Total	437.18
	4-Jul-18 Total	327.44
	5-Jul-18 Total	998.3
	6-Jul-18 Total	1312.23
	Grand Total	7138.34

 

[hitch_hiker]

You can try Excel's Subtotal; it will prepare a summary for you
1 is Grand Total
2 by each change
3 original detail and subtotal
I show the sum by change - 2
Select the Range
Use Data Outline Subtotal
at each change of date
sum
You do not enter the formulas.

N.B. You can post an extract of your sheet with the forum's tool XL2BB.

Thanks Dave,
I'm not sure how to do this, however I have every confidence I can play around as well as consult the help files. I think this will be easier and easier is always the best answer