Sum a Specific Column up to a Specific Row

[dsison18]

Is it possible to look for a specific column based on a column criteria and sum the column up to a specific row based on a row criteria?

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]March[/TD]
[TD]April[/TD]
[/TR]
[TR]
[TD]Yr. 1

[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]50[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]Yr. 2[/TD]
[TD]40[/TD]
[TD]80[/TD]
[TD]60[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Yr. 3[/TD]
[TD]30[/TD]
[TD]10[/TD]
[TD]80[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Yr. 4[/TD]
[TD]60[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]60[/TD]
[/TR]
</tbody>[/TABLE]

Month = March (Column criteria)
Year = Yr. 3 (Row criteria)

I want a formula that will look for the March column and then sum that column up to the Yr. 3 row (50+60+80 only). Is this possible?

 

[jorismoerings]

Hi and welcome to MrExcel,

Take a look at this:

Book1
	A	B	C	D	E
1		Jan	Feb	March	April
2	Yr. 1	10	20	50	70
3	Yr. 2	40	80	60	20
4	Yr. 3	30	10	80	40
5	Yr. 4	60	30	20	60
6
7
8
9	Yr. 3	March	190
Sheet2

Cell Formulas
Range	Formula
C9	=SUM(OFFSET($A$1,1,MATCH($B$9,$B$1:$E$1,0),MATCH($A$9,$A$2:$A$5,0),1))

Hope this helps.

 

[dsison18]

Hi, For some reason, it's not working and I don't know why. The months in my table extend 12 columns to December, so the month lookup range is B1:M1. The Years in my table go down 40 years. So, the year lookup range is A2:A41. The range that actually has the data is 40 rows and 12 columns. The top left cell is B2 and the bottom right corner is M41. I need to be able to sum a column of data based on which month I need, but only up to the year I need. Does this additional information change what the above formula should look like?

 

[jorismoerings]

Hi, can be changed but would need the references as well. I used A9 and B9 to put the year and month to search for, in.

 

[Nishant94]

I guess you have the data in range A1:D5
and your month criteria in B6 and yr criteria in A7 and you want your desired result in B7, then the formula would be as follow:

=SUMPRODUCT(INDEX(B2:D5,1,MATCH(B6,B1:D1,0)):INDEX(B2:D5,MATCH(A7,A2:A5,0),MATCH(B6,B1:D1,0)))

And if you want to sum up March from yr 2 to yr 3 or something similar to that and you have yr2 and yr3 in A7 and A8 respectively and month criteria in B6 then you can use:

=SUMPRODUCT(INDEX(B2:D5,MATCH(A7,A2:A5,0),MATCH(B6,B1:D1,0)):INDEX(B2:D5,MATCH(A8,A2:A5,0),MATCH(B6,B1:D1,0)))

 

[dsison18]

Thank you! Both solutions worked.