Sumif Index Match date range

[odonovanc]

I have seen a few threads on this but I cannot get any of the formulas to work for me. In cells A1:A100 I have various cities. In cell b2 is today's date. In cell c2 is 1 year ago.

I then have a "data" tab that has all these cities and data by month. The dates in the data tab are in cells J2:AG2. The data in J3:AG102.

Can someone help me write a formula that will match the cities in column a and then sum based on the date parameters?

 

[odonovanc]

Here is a sample of the data tab, modified.

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​
1​
2​		Dec-17​	Jan-18​	Feb-18​	Mar-18​	Apr-18​	May-18​	Jun-18​	Jul-18​	Aug-18​	Sep-18​	Oct-18​	Nov-18​	Dec-18​
3​	Austin	64,865.80​	67,761.27​	64,243.33​	52,414.79​	64,567.78​	77,105.07​	53,415.91​	66,155.08​	54,688.41​	47,672.44​	75,437.55​	66,663.28​	42,126.12​
4​	Boston	74,472.71​	77,061.55​	75,655.83​	103,983.40​	105,744.32​	129,533.54​	90,367.25​	95,308.40​	97,658.60​	71,324.88​	91,700.61​	91,938.51​	106,801.43​
5​	Chicago	2,460.36​	769.47​	3,764.59​	2,224.00​	2,705.84​	5,395.94​	5,227.95​	6,870.98​	1,781.04​	3,772.04​	2,125.85​	4,905.17​	4,081.52​
6​	Detroit	0.00​	0.00​	266.42​	0.00​	-​	0.00​	0.00​	0.00​	84.93​	0.00​	0.00​	-​	49,211.33​
7​	Dallas	0.00​	0.00​	0.00​	0.00​	-​	0.00​	0.00​	0.00​	0.00​	0.00​	0.00​	-​	43,219.26​

Sheet: Chart Data

 

[odonovanc]

Trying to get YTD to add in column D. But this will be using different dates at different times. So I want it to sum based on whatever days I put in B1 and C1.

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​		12/31/2018​	12/31/2017​	YTD
2​
3​	Austin	42126.12​	64865.8​
4​	Boston	106801.43​	74472.71​
5​	Chicago	4081.52​	2460.36​
6​	Detrot	49211.33​	0​
7​	Dallas	43219.26​	0​

Sheet: Sheet1

 

[Fluff]

As long as the dates in row 2 of the data tab are proper dates formatted to look like Dec-17, then this should work
=SUM(INDEX(Data!$J$3:$AG$102,MATCH(A3,Data!$A$3:$A$102,0),MATCH(1,(Data!$J$2:$AG$2<=$B$1)*(Data!$J$2:$AG$2>=$C$1),0)))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[odonovanc]

As long as the dates in row 2 of the data tab are proper dates formatted to look like Dec-17, then this should work
=SUM(INDEX(Data!$J$3:$AG$102,MATCH(A3,Data!$A$3:$A$102,0),MATCH(1,(Data!$J$2:$AG$2<=$B$1)*(Data!$J$2:$AG$2>=$C$1),0)))
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

Getting close. Using the data I already posted and this formula, I get Dec - 17 data returned to me. Not the sum between dates This is in cell d3:

=SUM(INDEX('Chart Data'!B3:N7,MATCH(A3,'Chart Data'!A3:A7,0),MATCH(1,('Chart Data'!B2:N2<=$B$1)*('Chart Data'!B2:N2>=$C$1),0)))

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​
1​		12/31/2018​	12/31/2017​	YTD
2​
3​	Austin	42126.12​	64865.8​	64,865.80​
4​	Boston	106801.43​	74472.71​
5​	Chicago	4081.52​	2460.36​
6​	Detroit	49211.33​	0​
7​	Dallas	43219.26​	0​

Sheet: Sheet1

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D
1		31/12/2018	31/12/2017	YTD
2
3	Austin	42126.12	64865.8	797116.8
4	Boston	106801.43	74472.71	1211551
5	Chicago	4081.52	2460.36	46084.75
6	Detroit	49211.33	0	49562.68
7	Dallas	43219.26	0	43219.26
Sheet2

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(('Chart Data'!$A$3:$A$7=A3)*('Chart Data'!$B$2:$N$2<=$B$1)*('Chart Data'!$B$2:$N$2>=$C$1),('Chart Data'!$B$3:$N$7))

 

[odonovanc]

Perfect. You are awesome. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback