# Sum a Specific Column up to a Specific Row

#### dsison18

##### New Member
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?

 Jan Feb March April Yr. 1 10 20 50 70 Yr. 2 40 80 60 20 Yr. 3 30 10 80 40 Yr. 4 60 30 20 60

<tbody>
</tbody>

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?

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi and welcome to MrExcel,

Take a look at this:

Book1
ABCDE
1JanFebMarchApril
2Yr. 110205070
3Yr. 240806020
4Yr. 330108040
5Yr. 460302060
6
7
8
9Yr. 3March190
Sheet2
Cell Formulas
RangeFormula
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.

Last edited:
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?

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.

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

Thank you! Both solutions worked.

Replies
6
Views
283
Replies
2
Views
115
Replies
26
Views
2K
Replies
4
Views
172
Replies
9
Views
271

1,216,100
Messages
6,128,824
Members
449,470
Latest member
Subhash Chand

### 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.

### Which adblocker are you using?

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

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