[Solved] Weekly Sum of Production in Excel

[mfaisal.ce]

Dear,

I have 2 columns
column b has months from jan to dec and column C has production values on monthly basis.
i need to calculate weekly production based on date given in A1.
Week starts from Sunday and ends in Saturday.

For example
consider A1 is 1st july 2024. it means the week is 30-06-24 sunday to 06-07-24 saturday. formula should sum the values of 1 day of june production and 6 days of july production to give output as weekly production.

regads,

 

[shamimarita]

Dear,

I have 2 columns
column b has months from jan to dec and column C has production values on monthly basis.
i need to calculate weekly production based on date given in A1.
Week starts from Sunday and ends in Saturday.

For example
consider A1 is 1st july 2024. it means the week is 30-06-24 sunday to 06-07-24 saturday. formula should sum the values of 1 day of june production and 6 days of july production to give output as weekly production.

regads,

Hello Faisal,

First, create a Daily Production table using the values of Months and Values.
In Column D: Total days in each month (31, 29, 31, etc.)
In Column E: Daily production values: =C2/D2

Then, you need to calculate the starting date of the week (Sunday):
Use the following formula to find the previous Sunday from the given date in A1: = A1 - WEEKDAY(A1, 2)

Finally, use the following formula to sum the production values for the week, considering both the previous and current month's daily production values:
=IF(MONTH(F2)=MONTH(F2+6), E2*7, E2*(DAY(EOMONTH(F2,0))-DAY(F2)+1) + E3*(7-(DAY(EOMONTH(F2,0))-DAY(F2)+1)))




Download the Excel File:

 

[mfaisal.ce]

Dear,
Thanks for your reply but it is not working or may be i am not able to understand. i will explain agian and attach my original file for correction.

Sheet- Summary
cell: E2:E7 (I need to calculate Budgeted production for each product in B4:B7 but only for week (Sunday to Saturday).
Date: A1 is having the date and it will change daily.
Each Month Budgeted Target is already allocated in B11:B26.
Consider Each month 30 days equally.

i tried to rearrange the formula u provided in my sheet but unable to get the results.

Kindly guide.

regards,

 

[mfaisal.ce]

Hello ,
I m waiting for the response.
Kindly update

 

[mfaisal.ce]

Hello ,
I m waiting for the response.
Kindly update

 

[shamimarita]

Hello ,
I m waiting for the response.
Kindly update

Hi,

I am working with your formula.

 

[shamimarita]

Dear,
Thanks for your reply but it is not working or may be i am not able to understand. i will explain agian and attach my original file for correction.

Sheet- Summary
cell: E2:E7 (I need to calculate Budgeted production for each product in B4:B7 but only for week (Sunday to Saturday).
Date: A1 is having the date and it will change daily.
Each Month Budgeted Target is already allocated in B11:B26.
Consider Each month 30 days equally.

i tried to rearrange the formula u provided in my sheet but unable to get the results.

Kindly guide.

regards,

Hello Faisal,

I updated the previous formula to calculate weekly production dynamically. To use this formula you will need to use the TEXT function in your Month column.As without using the same date format MATCH function cannot fetch the exact values.
Formula:
=TEXT(B2, "mmm-yyyy")

Formula:
=IF(MONTH(A2 - WEEKDAY(A2, 2)) = MONTH(A2 - WEEKDAY(A2, 2) + 6),INDEX(D213, MATCH(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C2:C13, 0)) / 30 * 7,(INDEX(D213, MATCH(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C2:C13, 0)) / 30 * (DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1)) +(INDEX(D213, MATCH(TEXT(A2 - WEEKDAY(A2, 2) + 7, "mmm-yyyy"), C2:C13, 0)) / 30 * (7 - (DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1))))

You can use VLOOKUP instead of INDEX-MATCH. But the criteria for the Date format will be the same.
=IF(
MONTH(A2 - WEEKDAY(A2, 2)) = MONTH(A2 - WEEKDAY(A2, 2) + 6),
VLOOKUP(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C213, 2, FALSE) / 30 * 7,
(
VLOOKUP(TEXT(A2 - WEEKDAY(A2, 2), "mmm-yyyy"), C213, 2, FALSE) / 30 *
(DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1)
) +
(
VLOOKUP(TEXT(A2 - WEEKDAY(A2, 2) + 7, "mmm-yyyy"), C213, 2, FALSE) / 30 *
(7 - (DAY(EOMONTH(A2 - WEEKDAY(A2, 2), 0)) - DAY(A2 - WEEKDAY(A2, 2)) + 1))
)
)

Download the Excel File and update your main sheet according to this update.

 

[mfaisal.ce]

Thanks a lot. It worked.

regards,

 

[shamimarita]

Thanks a lot. It worked.

regards,

You are most welcome. I'm glad to hear that it worked. Let's continue to help each other out. If you need any further assistance, feel free to reach out. Have a great day!

 

[mfaisal.ce]

Dear,

The above mentioned formula worked perfect as i highlighted earlier. Now, i have some new requirement in this formula.
1. Current formula gives me weekly production at once and that production is shown unless next week starts.
My requirement is weekly production should not be shown at once, it should be shown daily wise gradually. so that, i can compare what was my weekly production starting from sunday to saturday.

Example

SUNDAY 20 --> 20
MONDAY 50 --> 70
TUESDAY 30 --> 100
WEDNESDAY 50 --> 150
THRUSDAY 100 --> 250
FRIDAY 50 --> 300
SATURDAY 20 --> 320

I hope, i cleared my concern.

regards,
Faisal

 

[shamimarita]

Hello Faisal,

To achieve a gradual daily total, you can use this modified formula, assuming your dates are in Column A and production in Column B:

=IF(TEXT(A2,"dddd")="Sunday",B2, C1 + B2)

Here’s how it works:

• Place this formula in Column C (starting from C2) and drag it down.
• For each week, it resets on Sunday and cumulatively sums daily values.

This will provide your desired progressive weekly total. Let me know if further clarification is needed!

 

[mfaisal.ce]

Dear,

Thanks for your replay. may be i cound not explain properly.

I am attaching my reference file, Sheet "Summary". E4 has Budgeted value for whole week but this value remains same for whole week because it is calculated once and F4 value changes daily based on acutal value and progessively accumulate.


How to make the E4 cell calculate progressively for each week starting from sunday till saturday without additon of new cell.

I am sharing the acutal file , which i am using.

regards,

 

[shamimarita]

Hello Faisal,

Thank you for sharing your detailed requirements and the file. While I’m happy to provide suggestions and guidance, I encourage you to adapt the earlier solutions to your specific needs.

This can be achieved using a formula that tracks the current date and adjusts calculations accordingly. Here's how you can approach it:

=IF(TEXT(TODAY(),"dddd")="Sunday",B4,SUM(B$4:B4))

This assumes your daily production values are listed in Column B, starting from B4 for Sunday.
The formula resets each Sunday and accumulates the daily values progressively throughout the week.

 

[mfaisal.ce]

Thanks a lot for your support

regards,