I Need a Super SUMPRODUCT

[Bearcat Brew]

I am familiar with using SUMPRODUCT for summing with multiple criteria, but I am not sure if it works for what I am trying to do here. I would like to get running year-to-date totals for specified brands. Several years of monthly data will be in the sheet.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Nov-05	Dec-05	Jan-06	Feb-06	Mar-06	Apr-06	May-06	Jun-06	Jul-06	Aug-06	Sep-06	Oct-06	Nov-06	Dec-06	Jan-07	Feb-07
2	BrandA	12	11	0	191	275	131	144	36	25	18	15	14	6	2	6	1
3	BrandA	5	6	18	17	9	5	2	1	6	9	8	0	2	0	14	0
4	BrandA	5	2	0	0	0	0	0	0	0	0	8	0	0	0	0	6
5	BrandB	0	0	11	0	2	1	0	0	0	2	3	1	0	0	0	1
6	BrandB	6	2	1	1	0	3	0	0	0	0	2	2	11	3	5	9
7	BrandA	1	2	5	5	14	6	8	0	7	7	4	5	0	0	0	12
8	BrandC	83	94	0	0	0	0	0	2	0	0	86	97	119	106	112	36
9	BrandB	2	11	89	100	122	109	115	39	10	5	5	14	45	35	41	12
Sheet1

I would like the summary to be the YTD total as of the month the column represents for all of a specified brand.

Book3
	A	B	C	D
1		Jan-06	Feb-06	Mar-06
2	BrandA	23	236	534
3	BrandB	101	202	326
4	BrandC	0	0	0
Sheet2

So as an example, I would like C2 to be the total of all Brand A for January 2006 and February 2006. I don't believe SUMPRODUCT works for something like this since I need to sum not only multiple rows, but multiple columns as well. Possibly it works with some combination of INDEX/MATCH?

 

[onlyadrafter]

Hello,

Try using a Pivot Table

 

[Oaktree]

Also consider sorting your data and using data --> subtotals.

 

[Bearcat Brew]

The data in the first table is not stable over time: new lines may get inserted or appended by multiple users. That is why I was hoping for a formulaic approach.

 

[Oaktree]

Sheet2!B2=SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),Sheet1!D$2:D$100)

Copy Sheet2!B2 down and right.

 

[Bearcat Brew]

That formula only gives me the totals for that month, not the year-to-date totals. For January, I only need to return January, but for February, I need to return the sum of all January plus all February for each specified product, etc. I am thinking I need an intermediate sheet that does year-to-date totals for each line on which I can then do a standard sumif. Not quite optimal but I can't currently see another option

 

[Oaktree]

Sorry, didn't catch that before.

How about this?

Sheet2!B2=SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),Sheet1!D$2:D$100) as before and copied down

Sheet2!C2=SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),Sheet1!E$2:E$100)+B2 copied down and right

 

[Bearcat Brew]

Thanks, that will work for this. Next thought is to convert to brand share pecentages (divide by the sum of the entire column in January and the sum of the series of columns for February through December). In that case, the previous column is not just B2 since it will have been divided by the sum of all brands.

 

[xcellnoob]

Sorry, didn't catch that before.

How about this?

Sheet2!B2=SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),Sheet1!D$2:D$100) as before and copied down

Sheet2!C2=SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),Sheet1!E$2:E$100)+B2 copied down and right

Oaktree, not trying to hijack the thread, I would just like to know what the -- does in a formula? I can't find it anywhere. Just thought I'd ask.
Thanks/

 

[Bearcat Brew]

Forces the TRUE/FALSE resulting array to one of 1/0.