*URGENT*: Sum Multiple Columns with single criteria across a row?

[im2bz2p345]

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

	A	B	C
State
04-2011	04-2011
$19M	$30M
Draw	Draw
Sales	Sales
Total
Sales
AR
AZ
CA
CO
CT
DC
DE
FL
GA
IA
ID
IL
IN
KS
KY
LA
MA
MD
ME
MI
MN
MO
MT
NC
ND
NE
NH
NJ
NM
NY
OH
OK
OR
PA
RI
SC
SD
TN
TX
VA
VI
VT
WA
WI
WV
WY

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]4/1/2011[/TD]
[TD="align: right"]4/5/2011[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]$22,044,730[/TD]
[TD="align: right"]$21,965,644[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]$159,515[/TD]
[TD="align: right"]$155,023[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]$321,796[/TD]
[TD="align: right"]$349,151[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]$3,538,254[/TD]
[TD="align: right"]$3,884,198[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]$130,015[/TD]
[TD="align: right"]$145,712[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]$202,068[/TD]
[TD="align: right"]$203,551[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]$63,763[/TD]
[TD="align: right"]$60,450[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]$63,883[/TD]
[TD="align: right"]$64,074[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]$1,137,126[/TD]
[TD="align: right"]$1,020,718[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]$100,545[/TD]
[TD="align: right"]$104,780[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]$73,582[/TD]
[TD="align: right"]$77,117[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]$1,174,096[/TD]
[TD="align: right"]$1,141,760[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]$236,512[/TD]
[TD="align: right"]$247,063[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]$79,366[/TD]
[TD="align: right"]$87,957[/TD]

[TD="align: center"]22[/TD]

[TD="align: right"]$245,665[/TD]
[TD="align: right"]$241,764[/TD]

[TD="align: center"]23[/TD]

[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]

[TD="align: center"]24[/TD]

[TD="align: right"]$710,861[/TD]
[TD="align: right"]$675,126[/TD]

[TD="align: center"]25[/TD]

[TD="align: right"]$820,056[/TD]
[TD="align: right"]$755,640[/TD]

[TD="align: center"]26[/TD]

[TD="align: right"]$30,896[/TD]
[TD="align: right"]$34,915[/TD]

[TD="align: center"]27[/TD]

[TD="align: right"]$1,281,350[/TD]
[TD="align: right"]$1,206,276[/TD]

[TD="align: center"]28[/TD]

[TD="align: right"]$120,666[/TD]
[TD="align: right"]$1,299,323[/TD]

[TD="align: center"]29[/TD]

[TD="align: right"]$246,102[/TD]
[TD="align: right"]$261,102[/TD]

[TD="align: center"]30[/TD]

[TD="align: right"]$22,986[/TD]
[TD="align: right"]$24,769[/TD]

[TD="align: center"]31[/TD]

[TD="align: right"]$488,303[/TD]
[TD="align: right"]$484,269[/TD]

[TD="align: center"]32[/TD]

[TD="align: right"]$21,773[/TD]
[TD="align: right"]$23,473[/TD]

[TD="align: center"]33[/TD]

[TD="align: right"]$80,555[/TD]
[TD="align: right"]$84,786[/TD]

[TD="align: center"]34[/TD]

[TD="align: right"]$70,144[/TD]
[TD="align: right"]$73,597[/TD]

[TD="align: center"]35[/TD]

[TD="align: right"]$1,578,880[/TD]
[TD="align: right"]$1,553,914[/TD]

[TD="align: center"]36[/TD]

[TD="align: right"]$66,601[/TD]
[TD="align: right"]$72,231[/TD]

[TD="align: center"]37[/TD]

[TD="align: right"]$3,211,429[/TD]
[TD="align: right"]$3,140,964[/TD]

[TD="align: center"]38[/TD]

[TD="align: right"]$1,198,085[/TD]
[TD="align: right"]$1,164,075[/TD]

[TD="align: center"]39[/TD]

[TD="align: right"]$114,920[/TD]
[TD="align: right"]$120,460[/TD]

[TD="align: center"]40[/TD]

[TD="align: right"]$101,768[/TD]
[TD="align: right"]$115,929[/TD]

[TD="align: center"]41[/TD]

[TD="align: right"]$688,730[/TD]
[TD="align: right"]$687,847[/TD]

[TD="align: center"]42[/TD]

[TD="align: right"]$75,064[/TD]
[TD="align: right"]$74,592[/TD]

[TD="align: center"]43[/TD]

[TD="align: right"]$275,375[/TD]
[TD="align: right"]$264,313[/TD]

[TD="align: center"]44[/TD]

[TD="align: right"]$23,467[/TD]
[TD="align: right"]$25,385[/TD]

[TD="align: center"]45[/TD]

[TD="align: right"]$294,336[/TD]
[TD="align: right"]$288,853[/TD]

[TD="align: center"]46[/TD]

[TD="align: right"]$1,353,784[/TD]
[TD="align: right"]$1,290,237[/TD]

[TD="align: center"]47[/TD]

[TD="align: right"]$1,003,665[/TD]
[TD="align: right"]$936,865[/TD]

[TD="align: center"]48[/TD]

[TD="align: right"]$7,443[/TD]
[TD="align: right"]$8,209[/TD]

[TD="align: center"]49[/TD]

[TD="align: right"]$20,081[/TD]
[TD="align: right"]$21,122[/TD]

[TD="align: center"]50[/TD]

[TD="align: right"]$357,181[/TD]
[TD="align: right"]$395,808[/TD]

[TD="align: center"]51[/TD]

[TD="align: right"]$167,471[/TD]
[TD="align: right"]$181,598[/TD]

[TD="align: center"]52[/TD]

[TD="align: right"]$86,572[/TD]
[TD="align: right"]$86,648[/TD]

[TD="align: center"]53[/TD]

[TD="align: right"]$0[/TD]
[TD="align: right"]$0[/TD]

</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

 

[im2bz2p345]

Sorry for the double post - if it's not clear above, what I need is a SUM of the sales data for each state by month.

So basically what I have in row 2 is the MONTH-YEAR (in this format: MM-YYYY). For the month of April 2011 (04-2011), I need the sum of sales data of each state.

In my example above, I have only two columns that would be summed together for each state abbreviation (i.e. AR = $159,515 + $155,023 = $314, 538).. but in my actual data there is often 9+ columns of data that needs to be summed across for each state.

Hope this explains it better,

~ Im2bz2p345