How To Subtotal Weekly Data By Months

[Karthik-Excelsior]

Hi,

I've a set of weekly data as shown in the doc here I need a formula to fill the table that is to the right of the list with data related to that particular month and year.

Any help is much appreciated!

 

[paddydive]

Hi Karthik,

You can add 2 column with heading Year And month
Takeout year for every cell by using =Year(A2) &
Month by = Text(A2,"mmm")

Then use Pivot table with Month as column, Year as Row and Sum of Quantity in Values

Or other option is Just creat a column for month as mentioned above and in Pivot group the date with Year

Paddydive

 

[Matt Rogers]

Hi.

Pivot is a good idea.
If formulas then maybe like this:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Week	Quantity			Jan	Feb	Mar	Apr
2	01/01/2011	100		2009	19200	14300	15500	25500
3	01/14/2011	400		2010	10200	8300	9500	16500
4	01/27/2011	700		2011	1200	2300	3500	7500
5	02/09/2011	1000
6	02/22/2011	1300
7	03/07/2011	1600
8	03/20/2011	1900
9	04/02/2011	2200
10	04/15/2011	2500
11	04/28/2011	2800
12	01/01/2010	3100
Sheet

 

[Karthik-Excelsior]

Hi guys,

Thanks!

Actually, what I did was

- insert a column 'B' and entered '=TEXT(A2,"mmm-yy")'
- in the table, I entered the formula "=SUMIF($B$2:$B$32,CONCATENATE(F$1,""-"",$E2),$C$2:$C$32)"

This worked!

Best,

 

[Matt Rogers]

Hi.

Thanks for reply.
You have a solution and that's ok!