I Need a Super SUMPRODUCT

Bearcat Brew

New Member
Joined
Dec 7, 2004
Messages
40
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

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Also consider sorting your data and using data --> subtotals.
 

Bearcat Brew

New Member
Joined
Dec 7, 2004
Messages
40
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

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

Copy Sheet2!B2 down and right.
 

Bearcat Brew

New Member
Joined
Dec 7, 2004
Messages
40
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365

ADVERTISEMENT

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

New Member
Joined
Dec 7, 2004
Messages
40
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

Board Regular
Joined
Apr 6, 2005
Messages
188
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/
 

Forum statistics

Threads
1,136,345
Messages
5,675,225
Members
419,555
Latest member
Paddington

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top