Sum Receipts by Week Number

Enter below formula in cell D2:

=SUMPRODUCT((INT((A$2:A$100-DATE(YEAR(A$2:A$100),1,1)-WEEKDAY(A$2:A$100))/7)+2=C2)*(B$2:B$100))

This returns the SUM of column B where week number of dates in column A is the same as C2. Copying the formula to D3 will return the SUM of week number in C3.

Note: Formula considers week number as used in the formula " =WEEKNUM(A2,1)" as illustrated in column F ie. week begins on Sunday. This can be changed.

No requirement of a helper column in this.

Regards,

Amit Tandon

www.globaliconnect.com

If you are fine with making use of a helper column, then the following should help you resolve your problem.

Adjacent to your date column, you need to have a helper column (see column-A in image below) which will compute the week number of that date (see column-B in image below).

Now we have a column-E which has list of week number across the year.

In column-F you can then put the SUMIF formula to do the needful

Hope this Helps.

Hi,

See the file that I have made for your purpose. You can download it in this link below.

16/11/2017 - Bob

In column D and G I have given the first date of the week respectively, when the week begins with Sunday, and when it begins with Monday. Meantime please be careful to distinguish formulas in column E and column H. In column E I have apply formula that work when the week begins with Sunday, and in column H I have apply formula that work when the week begins with Monday.

Formula in column E, when the week begins with Sunday is this:

=SUMPRODUCT((YEAR($A$3:$A$1000)=YEAR($D3))*(ISOWEEKNUM($A$3:$A$1000+1)=WEEKNUM($D3,1))*($B$3:$B$1000))

Formula in column H, when the week begins with Monday is this:

=SUMPRODUCT((YEAR($A$3:$A$1000)=YEAR($G3))*(ISOWEEKNUM($A$3:$A$1000)=ISOWEEKNUM($G3))*($B$3:$B$1000))

or this:

=SUMPRODUCT((YEAR($A$3:$A$1000)=YEAR($G3))*(ISOWEEKNUM($A$3:$A$1000)=WEEKNUM($G3,2))*($B$3:$B$1000))

With those formula you don’t need to put a helper column, to find number of weeks and from this point to find results for which week. You need to have only a column when to give dates for each starting weeks and then to apply the formula I have given to you.

Hope this helps.

Regards,

IlirU

If my reply response to your questions then please mark it as Answer. Thank you!

Hi,

In a spare column, use the WEEKNUM() function to extract the week number.  Now create a Pivot Table.

Hi Bob,

Based on your description, I suggest you try the following format to check if meet your requirement:

=IF(WEEKDAY(A1,2)=7,TEXT(A1,SUM(B1:B7)))

Note: A1 is the first date on your column, and B1 is the first column of the payment.

If it couldn’t meet your requirement, could you please provide the following information?

1. The screenshot of the detailed sheet.
2. The formula which get #value! Error.

Thanks,

Qing