Sum a Specific Column up to a Specific Row

dsison18

New Member
Joined
Feb 17, 2016
Messages
3
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?

JanFebMarchApril
Yr. 1

10205070
Yr. 240806020
Yr. 330108040
Yr. 460302060

<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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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)))
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top