How to sum data in a column given adjacent column data

[griff384]

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
[TABLE="width: 685"]
<colgroup><col><col><col span="2"><col><col span="3"><col><col></colgroup><tbody>[TR]
[TD]7/15/2006 [/TD]
[TD]135.8[/TD]
[TD]0.002[/TD]
[TD]1.209[/TD]
[TD]0.016[/TD]
[TD]1.4[/TD]
[TD]1.3[/TD]
[TD]26.55[/TD]
[TD]9.2[/TD]
[TD]-7.9[/TD]
[/TR]
[TR]
[TD]7/16/2006[/TD]
[TD]147.1[/TD]
[TD]0.080[/TD]
[TD]1.136[/TD]
[TD]0.014[/TD]
[TD]2.3[/TD]
[TD]2.1[/TD]
[TD]37.19[/TD]
[TD]9.9[/TD]
[TD]106.4[/TD]
[/TR]
[TR]
[TD]7/17/2006[/TD]
[TD]143.8[/TD]
[TD]-0.023[/TD]
[TD]1.202[/TD]
[TD]0.011[/TD]
[TD]2.2[/TD]
[TD]2.1[/TD]
[TD]33.87[/TD]
[TD]9.9[/TD]
[TD]-33.3[/TD]
[/TR]
[TR]
[TD]7/18/2006[/TD]
[TD]138.2[/TD]
[TD]-0.040[/TD]
[TD]0.955[/TD]
[TD]0.008[/TD]
[TD]2.7[/TD]
[TD]2.6[/TD]
[TD]28.18[/TD]
[TD]10.0[/TD]
[TD]-56.9[/TD]
[/TR]
[TR]
[TD]7/19/2006[/TD]
[TD]154.2[/TD]
[TD]0.110[/TD]
[TD]1.256[/TD]
[TD]0.005[/TD]
[TD]3.7[/TD]
[TD]3.6[/TD]
[TD]44.23[/TD]
[TD]10.0[/TD]
[TD]160.5[/TD]
[/TR]
[TR]
[TD]7/20/2006[/TD]
[TD]134.2[/TD]
[TD]-0.139[/TD]
[TD]1.911[/TD]
[TD]0.003[/TD]
[TD]2.0[/TD]
[TD]1.9[/TD]
[TD]24.28[/TD]
[TD]9.8[/TD]
[TD]-199.5[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[bbott]

When I copy your table and formula, I get -30.7, not 0. Is your worksheet calculation set to automatic or manual?

 

[Caribeiro77]

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
[TABLE="width: 685"]
<tbody>[TR]
[TD]7/15/2006[/TD]
[TD]135.8[/TD]
[TD]0.002[/TD]
[TD]1.209[/TD]
[TD]0.016[/TD]
[TD]1.4[/TD]
[TD]1.3[/TD]
[TD]26.55[/TD]
[TD]9.2[/TD]
[TD]-7.9[/TD]
[/TR]
[TR]
[TD]7/16/2006[/TD]
[TD]147.1[/TD]
[TD]0.080[/TD]
[TD]1.136[/TD]
[TD]0.014[/TD]
[TD]2.3[/TD]
[TD]2.1[/TD]
[TD]37.19[/TD]
[TD]9.9[/TD]
[TD]106.4[/TD]
[/TR]
[TR]
[TD]7/17/2006[/TD]
[TD]143.8[/TD]
[TD]-0.023[/TD]
[TD]1.202[/TD]
[TD]0.011[/TD]
[TD]2.2[/TD]
[TD]2.1[/TD]
[TD]33.87[/TD]
[TD]9.9[/TD]
[TD]-33.3[/TD]
[/TR]
[TR]
[TD]7/18/2006[/TD]
[TD]138.2[/TD]
[TD]-0.040[/TD]
[TD]0.955[/TD]
[TD]0.008[/TD]
[TD]2.7[/TD]
[TD]2.6[/TD]
[TD]28.18[/TD]
[TD]10.0[/TD]
[TD]-56.9[/TD]
[/TR]
[TR]
[TD]7/19/2006[/TD]
[TD]154.2[/TD]
[TD]0.110[/TD]
[TD]1.256[/TD]
[TD]0.005[/TD]
[TD]3.7[/TD]
[TD]3.6[/TD]
[TD]44.23[/TD]
[TD]10.0[/TD]
[TD]160.5[/TD]
[/TR]
[TR]
[TD]7/20/2006[/TD]
[TD]134.2[/TD]
[TD]-0.139[/TD]
[TD]1.911[/TD]
[TD]0.003[/TD]
[TD]2.0[/TD]
[TD]1.9[/TD]
[TD]24.28[/TD]
[TD]9.8[/TD]
[TD]-199.5[/TD]
[/TR]
</tbody>[/TABLE]

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!

What about the SUMIFS:
=SUMIFS(K:K,'Ch 5 & 6'!B:B,">="&C37,'Ch 5 & 6'!B:B,"<="&C38)

 

[griff384]

When I copy your table and formula, I get -30.7, not 0. Is your worksheet calculation set to automatic or manual?

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.

 

[Caribeiro77]

<html><head><title>Excel Jeanie HTML</title></head><body>


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

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	15-06-2006	135.8	0.002	1.209	0.016	1.4	1.3	26.55	9.2	-7,9
2	16-06-2006	147.1	0.080	1.136	0.014	2.3	2.1	37.19	9.9	106,4
3	17-06-2006	143.8	-0.023	1.202	0.011	2.2	2.1	33.87	9.9	33,3
4	18-06-2006	138.2	-0.040	0.955	0.008	2.7	2.6	28.18	10.0	56,9
5	19-06-2006	154.2	0.110	1.256	0.005	3.7	3.6	44.23	10.0	160,5
6	20-06-2006	134.2	-0.139	1.911	0.003	2.0	1.9	24.28	9.8	199,5
7
8
9
10
11					15-06-2006	15-06-2006
12
13					-7,9
Sheet5

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


</body></html>

 

[griff384]

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!