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


[TABLE="width: 371"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]date[/TD]
[TD]tonnes[/TD]
[TD][/TD]
[TD]end of week[/TD]
[TD]total tonnes[/TD]
[/TR]
[TR]
[TD="align: right"]29/09/2015[/TD]
[TD="align: right"]77[/TD]
[TD][/TD]
[TD="align: right"]29/09/2015[/TD]
[TD="align: right"]2413[/TD]
[/TR]
[TR]
[TD="align: right"]30/09/2015[/TD]
[TD="align: right"]200[/TD]
[TD][/TD]
[TD="align: right"]6/10/2015[/TD]
[TD="align: right"]1782[/TD]
[/TR]
[TR]
[TD="align: right"]1/10/2015[/TD]
[TD="align: right"]22[/TD]
[TD][/TD]
[TD="align: right"]13/10/2015[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/10/2015[/TD]
[TD="align: right"]563[/TD]
[TD][/TD]
[TD="align: right"]20/10/2015[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]3/10/2015[/TD]
[TD="align: right"]554[/TD]
[TD][/TD]
[TD="align: right"]27/10/2015[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]4/10/2015[/TD]
[TD="align: right"]556[/TD]
[TD][/TD]
[TD="align: right"]3/11/2015[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]5/10/2015[/TD]
[TD="align: right"]441[/TD]
[TD][/TD]
[TD="align: right"]10/11/2015[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]6/10/2015[/TD]
[TD="align: right"]112[/TD]
[TD][/TD]
[TD="align: right"]17/11/2015[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]7/10/2015[/TD]
[TD="align: right"]445[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]8/10/2015[/TD]
[TD="align: right"]447[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]9/10/2015[/TD]
[TD="align: right"]778[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]10/10/2015[/TD]
[TD="align: right"]95[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2/11/2021[/TD]
[TD="align: right"]221[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]date[/TD]
[TD]tonnes[/TD]
[TD][/TD]
[TD]end of week[/TD]
[TD]total tonnes[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

9/29/2015​

[/TD]
[TD]

77​

[/TD]
[TD][/TD]
[TD]

9/29/2015​

[/TD]
[TD]

77​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

9/30/2015​

[/TD]
[TD]

200​

[/TD]
[TD][/TD]
[TD]

10/6/2015​

[/TD]
[TD]

2448​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

10/1/2015​

[/TD]
[TD]

22​

[/TD]
[TD][/TD]
[TD]

10/13/2015​

[/TD]
[TD]

4013​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

10/2/2015​

[/TD]
[TD]

563​

[/TD]
[TD][/TD]
[TD]

10/20/2015​

[/TD]
[TD]

3991​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

10/3/2015​

[/TD]
[TD]

554​

[/TD]
[TD][/TD]
[TD]

10/27/2015​

[/TD]
[TD]

3428​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

10/4/2015​

[/TD]
[TD]

556​

[/TD]
[TD][/TD]
[TD]

11/3/2015​

[/TD]
[TD]

2874​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

10/5/2015​

[/TD]
[TD]

441​

[/TD]
[TD][/TD]
[TD]

11/10/2015​

[/TD]
[TD]

2318​

[/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

10/6/2015​

[/TD]
[TD]

112​

[/TD]
[TD][/TD]
[TD]

11/17/2015​

[/TD]
[TD]

1877​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]

10/7/2015​

[/TD]
[TD]

445​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]

10/8/2015​

[/TD]
[TD]

447​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

10/9/2015​

[/TD]
[TD]

778​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

10/10/2015​

[/TD]
[TD]

95​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

11/2/2021​

[/TD]
[TD]

221​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.