Matching Dates & Summing

[VegasL]

Hi,

In tab A, I have date range
In tab B, I have weeks with a calendar

Tab C - Expected result.

What I'm trying to do is match up the date ranges from tab A with the "Weeks" in Tab B based on calendar, and end result should be to find the sum on tab c (column B)...explanation in column d for reference...please see attached file

How can this be done?

Thanks in advance

[63falcondude]

I would add a helper column in sheet A with a formula to show the week number (let's say this is in column C):

C2 =WEEKNUM(A2,21)

Then, you can use a SUMIF formula on sheet C:

B2 =SUMIF(A!C:C,A2,A!B:B)

[VegasL]

thanks 63falcondude, this seemed to have worked. what does it mean or why did you use 21, in formula c2=weeknum(a2,21)

[63falcondude]

You're welcome.

The second part of the WEEKNUM function is the return type. The return type controls which day of the week is used to begin a new week number.

The return types of 2 and 21 are both used for Monday. We used 21 here since you don't want Jan 1st to be week 1 (according to sheet B).

More on WEEKNUM here: https://support.office.com/en-us/art...1-b18c13c75340

If that solved your question, please mark this thread as SOLVED.