# [Solved]Weekly Sum of Production in Excel

#### mfaisal.ce

##### Member
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.

#### Attachments

• Book1.xlsx
12 KB · Views: 3
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.

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)))

#### Attachments

• Get Weekly Production.xlsx
16.6 KB · Views: 4
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,

#### Attachments

• Production Report-2 .xlsx
325 KB · Views: 4
Hello ,
I m waiting for the response.
Kindly update

Hello ,
I m waiting for the response.
Kindly update

Hello ,
I m waiting for the response.
Kindly update
Hi,

I am working with your formula.

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))
)
)

#### Attachments

• Updated Weekly Production.xlsx
12.9 KB · Views: 4
Last edited:
Thanks a lot. It worked.

regards,

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!

Members online
0
Guests online
57
Total visitors
57