Formula to consolidate weekly data

[rizmomin]

Hello:
Please refer to attached file.
I have data as shown for 3 years 2014.2016 and 2016 in column D,G and J.
I need formula to summarize weekly data for each year in column N,O and P, week starts Sun and ends on Sat

Let me know if you have any questions.
Thanks.
Riz

[Logit]

Using this formula:

Please Login or Register  to view this content.

Note this approach may require a re-alignment of your columns (vertically) depending on how you want the figures to appear visually. I'm sure there are other / better ways of accomplishing your goal.

[rizmomin]

Hello Logit:

Your formula works but I really need to compare weekly sales side by side for each year/
I wonder if this can be done by PIVOT Table.
Please help
Riz

[Logit]

Without a pivot table (I'm not very good with them yet) .... here is a manual method copying the cell contents over and aligning the columns

Please Login or Register  to view this content.

[dmg2016]

Hi rizmomin,


I attached a pivot table that analyzes sales by week number for 2014, 2015, and 2016. I changed the data format a bit to make it easier to work with. Also, I added in the formula =weeknumber(date) to group the weeks Sun-Sat in the pivot table..

DMG

[JohnTopley]

In N2

=SUMPRODUCT((INT((B$2:B$367-WEEKDAY(B$2:B$367)-DATE(YEAR(B$2:B$367),1,1))/7)+2=$M2)*1,D$2:D$367)

in O2

=SUMPRODUCT((INT((E$2:E$367-WEEKDAY(E$2:E$367)-DATE(YEAR(E$2:E$367),1,1))/7)+2=$M2)*1,G$2:G$367)

in P2

=SUMPRODUCT((INT((H$2:H$367-WEEKDAY(H$2:H$367)-DATE(YEAR(H$2:H$367),1,1))/7)+2=$M2)*1,J$2:J$367)

WEEKNUM cannot be used with ARRAY formulae so ned to use the above to determine week.

Alternative is to add columns alongside to dates with =WEEKNUM(date) then use

=SUMPRODUCT((D$2:D$367)*(weeknumcolumn=$M2))

COLUMN K has sample for 2014

=SUMPRODUCT((D$2:D$367)*(K$2:K$367=$M2))

[AlKey]

I think that taking the dates columns to another sheet will simplify this task.
Formula used on Sheet2 to copy date columns:

Please Login or Register  to view this content.

With Week number list in Col K from 1 to 53
Enter formula in L2 and copy across and down

Please Login or Register  to view this content.

[rizmomin]

Hello ALL:

Great help, thanks to all, i have lots of choices.
Thanks
Riz