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.
I would like the summary to be the YTD total as of the month the column represents for all of a specified brand.
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?
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?