im2bz2p345
Board Regular
- Joined
- Mar 31, 2008
- Messages
- 226
Hi all,
I am trying to finish up some analysis for an assignment and cannot figure out how to sum up multiple COLUMNS based on if a criteria matches across a ROW.
Below is an screenshot sample of my data (it's a very large dataset). Row 2 contains a bunch of MM-YYYY values. For example, if I want to sum the sales data for each state (Rows 8-53), if the rows match "04-2011".. how do I go about doing that? In this example, the formula would find that match "04-2011" in both column B & C and sum up the sales data for each state (in rows 8-53.
Hope this makes sense.
Excel 2010
<tbody>
</tbody>
I'm thinking that I need some sort of array formula/sum product formula, but I cannot figure out to how to structure it.
Ty in advance!
~ Im2bz2p345
I am trying to finish up some analysis for an assignment and cannot figure out how to sum up multiple COLUMNS based on if a criteria matches across a ROW.
Below is an screenshot sample of my data (it's a very large dataset). Row 2 contains a bunch of MM-YYYY values. For example, if I want to sum the sales data for each state (Rows 8-53), if the rows match "04-2011".. how do I go about doing that? In this example, the formula would find that match "04-2011" in both column B & C and sum up the sales data for each state (in rows 8-53.
Hope this makes sense.
Excel 2010
A | B | C | |
---|---|---|---|
1 | State | 4/1/2011 | 4/5/2011 |
2 | 04-2011 | 04-2011 | |
3 | $19M | $30M | |
4 | Draw | Draw | |
5 | Sales | Sales | |
6 | Total | $22,044,730 | $21,965,644 |
7 | Sales | ||
8 | AR | $159,515 | $155,023 |
9 | AZ | $321,796 | $349,151 |
10 | CA | $3,538,254 | $3,884,198 |
11 | CO | $130,015 | $145,712 |
12 | CT | $202,068 | $203,551 |
13 | DC | $63,763 | $60,450 |
14 | DE | $63,883 | $64,074 |
15 | FL | $0 | $0 |
16 | GA | $1,137,126 | $1,020,718 |
17 | IA | $100,545 | $104,780 |
18 | ID | $73,582 | $77,117 |
19 | IL | $1,174,096 | $1,141,760 |
20 | IN | $236,512 | $247,063 |
21 | KS | $79,366 | $87,957 |
22 | KY | $245,665 | $241,764 |
23 | LA | $0 | $0 |
24 | MA | $710,861 | $675,126 |
25 | MD | $820,056 | $755,640 |
26 | ME | $30,896 | $34,915 |
27 | MI | $1,281,350 | $1,206,276 |
28 | MN | $120,666 | $1,299,323 |
29 | MO | $246,102 | $261,102 |
30 | MT | $22,986 | $24,769 |
31 | NC | $488,303 | $484,269 |
32 | ND | $21,773 | $23,473 |
33 | NE | $80,555 | $84,786 |
34 | NH | $70,144 | $73,597 |
35 | NJ | $1,578,880 | $1,553,914 |
36 | NM | $66,601 | $72,231 |
37 | NY | $3,211,429 | $3,140,964 |
38 | OH | $1,198,085 | $1,164,075 |
39 | OK | $114,920 | $120,460 |
40 | OR | $101,768 | $115,929 |
41 | PA | $688,730 | $687,847 |
42 | RI | $75,064 | $74,592 |
43 | SC | $275,375 | $264,313 |
44 | SD | $23,467 | $25,385 |
45 | TN | $294,336 | $288,853 |
46 | TX | $1,353,784 | $1,290,237 |
47 | VA | $1,003,665 | $936,865 |
48 | VI | $7,443 | $8,209 |
49 | VT | $20,081 | $21,122 |
50 | WA | $357,181 | $395,808 |
51 | WI | $167,471 | $181,598 |
52 | WV | $86,572 | $86,648 |
53 | WY | $0 | $0 |
<tbody>
</tbody>
Sheet1
I'm thinking that I need some sort of array formula/sum product formula, but I cannot figure out to how to structure it.
Ty in advance!
~ Im2bz2p345
Last edited: