How to sum data in a column given adjacent column data

G

griff384

New Member
Joined
Jun 23, 2014
Messages
3
I want to sum values in column K given a range of dates from column B. Using the Sum, indirect, offset and match functions.
B C D E F G H I J K
7/15/2006 135.80.0021.2090.0161.41.326.559.2-7.9
7/16/2006147.10.0801.1360.0142.32.137.199.9106.4
7/17/2006143.8-0.0231.2020.0112.22.133.879.9-33.3
7/18/2006138.2-0.0400.9550.0082.72.628.1810.0-56.9
7/19/2006154.20.1101.2560.0053.73.644.2310.0160.5
7/20/2006134.2-0.1391.9110.0032.01.924.289.8-199.5

<colgroup><col><col><col span="2"><col><col span="3"><col><col></colgroup><tbody>
</tbody>

So I have a date range, in this case 7/15/2006 and ending at 7/20/2006 and I want to use those parameters to reference and sum the corresponding values in column K as shown. This part of the spreadsheet begins at row 51 and ends at row 56. The formula needs to be variable, I've tried doing it as such... =SUM(INDIRECT("K"&MATCH(C37,'Ch 5 & 6'!B1:B56,0)&":K"&MATCH(C38,'Ch 5 & 6'!B1:B56,0)))

But it's giving me a value of 0 and the practice worksheet tells me I need to embed Match within indirect within offset within sum. Any help would be greatly appreciated. Thank you!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
When I copy your table and formula, I get -30.7, not 0. Is your worksheet calculation set to automatic or manual?
 
Upvote 0
griff384 said:
I want to sum values in column K given a range of dates from column B. Using the Sum, indirect, offset and match functions.
B C D E F G H I J K
7/15/2006135.80.0021.2090.0161.41.326.559.2-7.9
7/16/2006147.10.0801.1360.0142.32.137.199.9106.4
7/17/2006143.8-0.0231.2020.0112.22.133.879.9-33.3
7/18/2006138.2-0.0400.9550.0082.72.628.1810.0-56.9
7/19/2006154.20.1101.2560.0053.73.644.2310.0160.5
7/20/2006134.2-0.1391.9110.0032.01.924.289.8-199.5

<tbody>
</tbody>

So I have a date range, in this case 7/15/2006 and ending at 7/20/2006 and I want to use those parameters to reference and sum the corresponding values in column K as shown. This part of the spreadsheet begins at row 51 and ends at row 56. The formula needs to be variable, I've tried doing it as such... =SUM(INDIRECT("K"&MATCH(C37,'Ch 5 & 6'!B1:B56,0)&":K"&MATCH(C38,'Ch 5 & 6'!B1:B56,0)))

But it's giving me a value of 0 and the practice worksheet tells me I need to embed Match within indirect within offset within sum. Any help would be greatly appreciated. Thank you!
Click to expand...

What about the SUMIFS:
=SUMIFS(K:K,'Ch 5 & 6'!B:B,">="&C37,'Ch 5 & 6'!B:B,"<="&C38)
 
Upvote 0
bbott said:
When I copy your table and formula, I get -30.7, not 0. Is your worksheet calculation set to automatic or manual?
Click to expand...

It is set to automatic. I'm trying to refresh it and i'm still getting 0, but -30.7 is the value I should be getting. I've also tried the sumifs, still getting 0, I have a weird student version of excel, but it should behave the same as the 2007 version.
 
Last edited:
Upvote 0
<html><head><title>Excel Jeanie HTML</title></head><body>


<!-- ######### Start Erzeugter Html Code zum Kopieren ########## -->


Excel Workbook
ABCDEFGHIJ
115-06-2006135.80.0021.2090.0161.41.326.559.2-7,9
216-06-2006147.10.0801.1360.0142.32.137.199.9106,4
317-06-2006143.8-0.0231.2020.0112.22.133.879.933,3
418-06-2006138.2-0.0400.9550.0082.72.628.1810.056,9
519-06-2006154.20.1101.2560.0053.73.644.2310.0160,5
620-06-2006134.2-0.1391.9110.0032.01.924.289.8199,5
7
8
9
10
1115-06-200615-06-2006
12
13-7,9
Sheet5







<!-- ######### Ende Erzeugter Html Code zum Kopieren ########## -->


</body></html>
 
Upvote 0
Thank you Caribeiro that seems to have made it work though I'm not sure why the other solutions did not. A solution using Indirect, Offset and Match is also welcome. However I have seemed to solve me issue at the moment. Thank You all!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Need VBA help Cut and paste in to another locations if data and condition matches.
Replies
1
Views
677
Kishan
K

Forum statistics

Threads
1,214,583
Messages
6,120,383
Members
448,956
Latest member
JPav

Share this page

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