Sum data from last week to first week of year

[gustavoavila]

Hello folks,

I have a little problem. I'm trying to summarize all info from last week to the first week of year, like an annual accumulation.

I use this formula to summarize data from specific weeknum (last week), but I need to use something like a for or while function to define a range of weeknums (first week of year to last week according today() value)

=SUMPRODUCT(--(DialySummaryCL[Project]=[@Project]);--(DialySummaryCL[CL Weeknum]=WEEKNUM(TODAY())-1);DialySummaryCL[Recived])

Very appreciate your help

 

[Akashwani]

Hi,

I don't think that you can use WEEKNUM in an array (I may be mistaken), so you may need to add a "Helper" column to get the weeknumber.

Does this work?...

Excel Workbook
	A	B	C	D	E
1	Date	Values	Weeknum	Sum
2	01/01/2013	100	1	700
3	15/01/2013	100	3
4	29/01/2013	100	5
5	12/02/2013	100	7
6	26/02/2013	100	9
7	12/03/2013	100	11
8	26/03/2013	100	13
9	09/04/2013	100	15
10	23/04/2013	100	17
11	07/05/2013	100	19
12	21/05/2013	100	21
13	04/06/2013	100	23
14
Sheet6

The formula in C2 needs to be copied down and the formula in D1 will need the cell references changed to suit your layout.

I hope that helps.

Ak