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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,486
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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:

dsison18

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

Well-known Member
Joined
Jul 4, 2014
Messages
1,486
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
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

Well-known Member
Joined
May 8, 2015
Messages
514
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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)))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,530
Messages
5,837,907
Members
430,520
Latest member
VenkateshRajaganesan

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
Top