# I Need a Super SUMPRODUCT

#### Bearcat Brew

##### New Member
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
ABCDEFGHIJKLMNOPQ
1Nov-05Dec-05Jan-06Feb-06Mar-06Apr-06May-06Jun-06Jul-06Aug-06Sep-06Oct-06Nov-06Dec-06Jan-07Feb-07
2BrandA1211019127513114436251815146261
3BrandA5618179521698020140
4BrandA5200000000800006
5BrandB00110210002310001
6BrandB62110300002211359
7BrandA125514680774500012
8BrandC839400000200869711910611236
9BrandB211891001221091153910551445354112
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
ABCD
1Jan-06Feb-06Mar-06
2BrandA23236534
3BrandB101202326
4BrandC000
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?

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Hello,

Try using a Pivot Table

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

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.

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

Copy Sheet2!B2 down and right.

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

Sorry, didn't catch that before.

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

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.

Sorry, didn't catch that before.

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/

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

Replies
3
Views
389
Replies
4
Views
496
Replies
3
Views
302
Replies
2
Views
859
Replies
4
Views
637

1,219,935
Messages
6,151,065
Members
451,006
Latest member
dhinze84

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back