SUM Between Two Dates

[lynette1978]

I have the following data.
TAB 1:
[TABLE="width: 196"]
<tbody>[TR]
[TD][/TD]
[TD][TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Location[/TD]
[TD]Month[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]San Diego[/TD]
[TD]Feb-18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col><col span="2"></colgroup>[/TABLE]

TAB 2:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Location[/TD]
[TD]Season Start[/TD]
[TD]Season End[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]San Diego[/TD]
[TD]10/1/2017[/TD]
[TD]12/31/2017[/TD]
[TD]$153.00[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]San Diego[/TD]
[TD]1/1/2018[/TD]
[TD]7/31/2018[/TD]
[TD]$167.00[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]San Diego[/TD]
[TD]8/1/2018[/TD]
[TD]9/30/2018[/TD]
[TD]$153.00[/TD]
[/TR]
</tbody>[/TABLE]

On TAB 1, in cell C2, I'm trying to pull in the rate from TAB 2, based on the month in TAB 1, cell B2. So in this example, it would pull in the rate of $167.00. I've tried using SUMIFS with ">=", but it pulls in the first rate where the month on TAB 1 is greater than the date in column B on TAB 2. Is there a way to do this?

 

[Scott T]

This is an array formula and must be committed with CONTROL+SHIFT+ENTER. If done correctly Excel will put {} around the formula. Change Sheet7 to the name of your tab 2

=IFERROR(INDEX(Sheet7!D2:D4,SMALL(IF(A2=Sheet7!A2:A4,IF(B2<=Sheet7!C2:C4,IF(B2>=Sheet7!B2:B4,ROW(Sheet7!D2:D4)-ROW(Sheet7!D2)+1))),1)),"not found")

 

[FormR]

I have the following data.

Hi, if your data is sorted by Location and then by "Start Date" in ascending order, then here is another option that you can test.

Excel 2013/2016
	A	B	C
1	Location	Month	Rate
2	San Diego	Feb-18	167
Tab1

Cell Formulas
Range	Formula
C2	=LOOKUP(B2,'Tab2'!$B$2:$B$100/('Tab2'!$A$2:$A$100=A2),'Tab2'!$D$2:$D$100)

Excel 2013/2016
	A	B	C	D
1	Location	Season Start	Season End	Rate
2	San Diego	10/01/2017	12/31/2017	153
3	San Diego	01/01/2018	07/31/2018	167
4	San Diego	08/01/2018	09/30/2018	153
Tab2

 

[lynette1978]

Thank you. It worked beautifully.

 

[Mike Szczesny]

Can you use something like this? It is not perfect for your situation. Instead of Feb-18, I used 2/18/2018. I am sure there is an expert out there who can figure this part out. Need to use Cntrl+Shift+Enter.
[TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"] =INDEX($D$2:$D$4,MATCH(1,(($B$7>=$B$2:$B$4)*($B$7<=$C$2:$C$4)),0))[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 288"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD]Season Start[/TD]
[TD]Season End[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD="align: right"]10/1/2017[/TD]
[TD="align: right"]12/31/2017[/TD]
[TD="align: right"]$1.00[/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]7/31/2018[/TD]
[TD="align: right"]$2.00[/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD="align: right"]8/1/2018[/TD]
[TD="align: right"]9/30/2018[/TD]
[TD="align: right"]$3.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Month[/TD]
[TD]Rate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]San Diego[/TD]
[TD="align: right"]2/18/2018[/TD]
[TD="align: right"]$2.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]