How to Sum Range Based on a Date?

[LEORITY]

Hello! New to the forum here...

My database shows the production of different products per day in tonnes and we report these tonnes per week (week 33, week 34, etc).
I would like to summarise the sum of production for each product per week on the front page of the report like WTD (Week To Date), all based on the report's date.
For instance, say 20/08/2015. This date is week 34 which started on 17/08 and will end on 23/08, so I need to report the sum from 17/08 to 20/08. Next day I open the spreadsheet, it will sum automatically from 17/08 to 21/08, but when that week is over, I need to start summing again the WTD tonnes for the next week.
Any ideas will be super welcomed.
Thank you so much!!!


Sent from my iphone using Tapatalk

[José Augusto]

Hi

Use

Please Login or Register  to view this content.

Regards

[kvsrinivasamurthy]

Sheet1 A3:M3 dates, A4:M4 are values
Formula

Please Login or Register  to view this content.

[LEORITY]

I will try both today and let you know the outcome!

Thanks guys!


Sent from my iphone using Tapatalk

[LEORITY]

Hey guys,

I used the following slightly adjusted formula kvsrinivasamurthy posted (which is the same as the Jose Augusto).

OriginalOne=SUMIFS(Sheet1!$A$4:$M$4,Sheet1!$A$3:$M$3,">="&(TODAY()-WEEKDAY(TODAY(),2)+1),Sheet1!$A$3:$M$3,"<="&TODAY())

MyOne=SUMIFS(Sheet1!$C$9:$AG$9,Sheet1!$C$6:$AG$6,">="&($B$8-WEEKDAY($B$8,2)+1),Sheet1!C$6:AG$6,"<="&$B$8)
Where $B$8=TODAY()-1

I have one more doubt and I really need your smart thoughts.

It is a similar case. My database gives me the annual budget by month. I want to report the Week To Date (WTD) budget.

Let's say my budget for August is $3,100. But August started on a Saturday (1/8/2015), so the weeks for August are as follows (my weeks are Monday to Sunday - I always report next morning of each day, 7 days a week):

Week 31: 2 days, formula has to give me: 3100*2/(31days*2)=$100
Week 32: 7 days, formula has to give me: 3100*7/(31days*7)=$100
Week 33: 7 days, formula has to give me: 3100*7/(31days*7)=$100
Week 34: 7 days, formula has to give me: 3100*7/(31days*7)=$100
Week 35: 7 days, formula has to give me: 3100*7/(31days*7)=$100
Week 36: 1 day, formula has to give me: 3100*1/(31days*1)=$100

When the date is 02/08/2015, I want to see on the budget cell, the average budget for that weeek (I know it will always be $100), but I need a formula that will recognize the week based on today's date minus 1 and will calculate it automatically (like the examples above)

Hopefully it is well explained... Let me know if you need me to clarify better.

Thank you so much guys!

[kvsrinivasamurthy]

Post a sample file.

[LEORITY]

Hi, attached is the sample file.

Book1.xlsx

I always report cumulatively what happened on the previous day (from the 1st day of the month), so after a week I will have the previous day and week info. In August, the month starts on Saturday (week 31). Week 31 will finish on Sunday, so I will have 2 days to report on week 31 for the month of August. Going forward, week 32 starts on Monday (3 August) so I will have a new budget for the week, and so on.

The results I want are on row 11.

Thank you so much.

[kvsrinivasamurthy]

In B12 , then dragged

Please Login or Register  to view this content.

[LEORITY]

In B12 , then dragged

Please Login or Register  to view this content.

Thank you very much! It worked flawlessly...
Best regards,
Leonardo


Sent from my iphone using Tapatalk