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

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,284
Hi and welcome to MrExcel,

Take a look at this:
<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style=";">Jan</td><td style=";">Feb</td><td style=";">March</td><td style=";">April</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Yr. 1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">20</td><td style="text-align: right;;">50</td><td style="text-align: right;;">70</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Yr. 2</td><td style="text-align: right;;">40</td><td style="text-align: right;;">80</td><td style="text-align: right;;">60</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Yr. 3</td><td style="text-align: right;;">30</td><td style="text-align: right;;">10</td><td style="text-align: right;;">80</td><td style="text-align: right;;">40</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Yr. 4</td><td style="text-align: right;;">60</td><td style="text-align: right;;">30</td><td style="text-align: right;;">20</td><td style="text-align: right;;">60</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Yr. 3</td><td style=";">March</td><td style="text-align: right;;">190</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C9</th><td style="text-align:left">=SUM(<font color="Blue">OFFSET(<font color="Red">$A$1,1,MATCH(<font color="Green">$B$9,$B$1:$E$1,0</font>),MATCH(<font color="Green">$A$9,$A$2:$A$5,0</font>),1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />



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,284
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
512
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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