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?

 

[Oaktree]

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.

I would do that as a second step. Keep the totals as before, then on another sheet (or at least another area of the same sheet), perform your % calculations.

 

[seenfresh]

Bearcat

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

Could you explain this further? Does it simply mean that because the equation is set up for a TRUE/FALSE variable; the 1/0 would relate the answer to a numerical solution instead of the T/F as the answer?

Would appreciate any help!

Thanks in advance

Sean

 

[Oaktree]

Hi, Sean. You might have a look at Aladin's "hall of fame" writeup on condition-driven computing here:

http://www.mrexcel.com/wwwboard/messages/8961.html

He explains the --'s and a whole bunch of other things you may find interesting and useful.

edit: seems I misspoke. Aladin doesn't explain the --'s in there, but it's still a good read. For --'s, try this thread instead: http://www.mrexcel.com/board2/viewtopic.php?t=179748

 

[seenfresh]

Thanks Oaktree!

Cheers!