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]

=SUMIFS(INDEX('Data'!J3:AG102,MATCH(A1,Sheet1!$A1:$A100,0),0),'Data'!J3:AG102,"<=" & B2,'Data'!J2:AG2,">=" & $C2)

This is the formula I keep trying but I get value. What am I keying in wrong?

 

[Fluff]

Which column on the Data sheet has the cities?

 

[odonovanc]

Which column on the Data sheet has the cities?

Column A

 

[Fluff]

Maybe

Excel 2013/2016
	A	B	C
1	Cities	11/01/2019	11/01/2018
2	Bath	133
3	Swindon	104
4	Chippenham	109
Sheet4

Cell Formulas
Range	Formula
B2	{=SUM(INDEX(Data!$J$3:$AG$102,MATCH(A2,Data!$A$3:$A$102,0),MATCH(1,(Data!$J$2:$AG$2<=$B$1)*(Data!$J$2:$AG$2>=$C$1),0)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[odonovanc]

I'm getting N/A

 

[odonovanc]

Still getting value message after correcting to reference my date cells.

 

[Fluff]

Can you post a sample of the sheet where the formula is located?
There are add-ins available here to help do that https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[odonovanc]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[odonovanc]

Does not look like that worked. Let me play with some more.