Total by week (and month) not adding up? Please help!

[Watcher05]

Hi all

I've been trying to use the formula for adding up weekly totals and using Mr Excels formula/example on youtube: https://www.youtube.com/watch?v=ZbEmZErWdvY

=SUMIFS($B$2:$B$14,$A$2:$A$14,">="&D2,A2:A14,"<"&D3)



I cannot for some reason copy the actual formula down any more then two cells. I know I'm doing something wrong which is simple!

In my example spreadsheet is very basic i plan to use this against several hundred/thousand transactions to try and workout weekly totals for tonnages for a local government weighbridge where everyone brings their waste to dump.

Also there will be multiple dates that will be the same. Will this formula work on duplicate dates as well?

I appreciate your feedback and help!

https://drive.google.com/open?id=0BwEPI8y2KRcZaXVjTEVibTQ2YWM

thanks - Daniel

date	tonnes		end of week	total tonnes
29/09/2015	77		29/09/2015	2413
30/09/2015	200		6/10/2015	1782
1/10/2015	22		13/10/2015	0
2/10/2015	563		20/10/2015	0
3/10/2015	554		27/10/2015	0
4/10/2015	556		3/11/2015	0
5/10/2015	441		10/11/2015	0
6/10/2015	112		17/11/2015	0
7/10/2015	445			0
8/10/2015	447			0
9/10/2015	778			0
10/10/2015	95			0
2/11/2021	221

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[steve the fish]

There wasnt too much wrong with what you had save for the absolution of the 2nd date range A2:A14. That said id use:

=SUMIFS(B:B,A:A,">"&D2-7,A:A,"<="&D2)

because SUMIFS can cope with full column references and then will work if you add more data. I wouldnt expect everything to add up bacuse one of your dates in column A is in the yesr 2021 which is not in your results column. Finally it will allow for multiple dates the same.

 

[Watcher05]

There wasnt too much wrong with what you had save for the absolution of the 2nd date range A2:A14. That said id use:

=SUMIFS(B:B,A:A,">"&D2-7,A:A,"<="&D2)

because SUMIFS can cope with full column references and then will work if you add more data. I wouldnt expect everything to add up bacuse one of your dates in column A is in the yesr 2021 which is not in your results column. Finally it will allow for multiple dates the same.

Thanks for for your help but to confirm, in your formula/example are the cells both "D2", or should one be be D3?

appreacite your feedback!

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​
1​	date	tonnes		end of week	total tonnes
2​	9/29/2015​	77​		9/29/2015​	77​
3​	9/30/2015​	200​		10/6/2015​	2448​
4​	10/1/2015​	22​		10/13/2015​	4013​
5​	10/2/2015​	563​		10/20/2015​	3991​
6​	10/3/2015​	554​		10/27/2015​	3428​
7​	10/4/2015​	556​		11/3/2015​	2874​
8​	10/5/2015​	441​		11/10/2015​	2318​
9​	10/6/2015​	112​		11/17/2015​	1877​
10​	10/7/2015​	445​			0​
11​	10/8/2015​	447​			0​
12​	10/9/2015​	778​			0​
13​	10/10/2015​	95​			0​
14​	11/2/2021​	221​

<tbody>
</tbody>

e2=SUMIFS($B$2:$B$14,$A$2:$A$14,">="&A2,$A$2:$A$14,"<="&D2) copy down

 

[steve the fish]

Thanks for for your help but to confirm, in your formula/example are the cells both "D2", or should one be be D3?

appreacite your feedback!

As it says end of week ive used greater than D2-7 for the start of the week and less than or equal to D2 for the end of the week. That gives you a week window.