Sumif Index Match date range

odonovanc

Board Regular
Joined
Oct 4, 2017
Messages
60
Office Version
  1. 365
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?
 
Here is a sample of the data tab, modified.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][th]
E
[/th][th]
F
[/th][th]
G
[/th][th]
H
[/th][th]
I
[/th][th]
J
[/th][th]
K
[/th][th]
L
[/th][th]
M
[/th][th]
N
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2
[/td][td][/td][td]
Dec-17​
[/td][td]
Jan-18​
[/td][td]
Feb-18​
[/td][td]
Mar-18​
[/td][td]
Apr-18​
[/td][td]
May-18​
[/td][td]
Jun-18​
[/td][td]
Jul-18​
[/td][td]
Aug-18​
[/td][td]
Sep-18​
[/td][td]
Oct-18​
[/td][td]
Nov-18​
[/td][td]
Dec-18​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td=bgcolor:#BFBFBF]Austin[/td][td=bgcolor:#BFBFBF]
64,865.80​
[/td][td=bgcolor:#BFBFBF]
67,761.27​
[/td][td=bgcolor:#BFBFBF]
64,243.33​
[/td][td=bgcolor:#BFBFBF]
52,414.79​
[/td][td=bgcolor:#BFBFBF]
64,567.78​
[/td][td=bgcolor:#BFBFBF]
77,105.07​
[/td][td=bgcolor:#BFBFBF]
53,415.91​
[/td][td=bgcolor:#BFBFBF]
66,155.08​
[/td][td=bgcolor:#BFBFBF]
54,688.41​
[/td][td=bgcolor:#BFBFBF]
47,672.44​
[/td][td=bgcolor:#BFBFBF]
75,437.55​
[/td][td=bgcolor:#BFBFBF]
66,663.28​
[/td][td=bgcolor:#BFBFBF]
42,126.12​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]Boston[/td][td]
74,472.71​
[/td][td]
77,061.55​
[/td][td]
75,655.83​
[/td][td]
103,983.40​
[/td][td]
105,744.32​
[/td][td]
129,533.54​
[/td][td]
90,367.25​
[/td][td]
95,308.40​
[/td][td]
97,658.60​
[/td][td]
71,324.88​
[/td][td]
91,700.61​
[/td][td]
91,938.51​
[/td][td]
106,801.43​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td=bgcolor:#BFBFBF]Chicago[/td][td=bgcolor:#BFBFBF]
2,460.36​
[/td][td=bgcolor:#BFBFBF]
769.47​
[/td][td=bgcolor:#BFBFBF]
3,764.59​
[/td][td=bgcolor:#BFBFBF]
2,224.00​
[/td][td=bgcolor:#BFBFBF]
2,705.84​
[/td][td=bgcolor:#BFBFBF]
5,395.94​
[/td][td=bgcolor:#BFBFBF]
5,227.95​
[/td][td=bgcolor:#BFBFBF]
6,870.98​
[/td][td=bgcolor:#BFBFBF]
1,781.04​
[/td][td=bgcolor:#BFBFBF]
3,772.04​
[/td][td=bgcolor:#BFBFBF]
2,125.85​
[/td][td=bgcolor:#BFBFBF]
4,905.17​
[/td][td=bgcolor:#BFBFBF]
4,081.52​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]Detroit[/td][td]
0.00​
[/td][td]
0.00​
[/td][td]
266.42​
[/td][td]
0.00​
[/td][td]
-​
[/td][td]
0.00​
[/td][td]
0.00​
[/td][td]
0.00​
[/td][td]
84.93​
[/td][td]
0.00​
[/td][td]
0.00​
[/td][td]
-​
[/td][td]
49,211.33​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td=bgcolor:#BFBFBF]Dallas[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
-​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
0.00​
[/td][td=bgcolor:#BFBFBF]
-​
[/td][td=bgcolor:#BFBFBF]
43,219.26​
[/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Chart Data[/td][/tr][/table]
 
Upvote 0

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td][/td][td=bgcolor:#FFFF00]
12/31/2018​
[/td][td]
12/31/2017​
[/td][td]YTD[/td][/tr]


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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]Austin[/td][td]
42126.12​
[/td][td]
64865.8​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]Boston[/td][td]
106801.43​
[/td][td]
74472.71​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]Chicago[/td][td]
4081.52​
[/td][td]
2460.36​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]Detrot[/td][td]
49211.33​
[/td][td]
0​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]Dallas[/td][td]
43219.26​
[/td][td]
0​
[/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
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
 
Upvote 0
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A
[/th][th]
B
[/th][th]
C
[/th][th]
D
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1
[/td][td][/td][td=bgcolor:#FFFF00]
12/31/2018​
[/td][td]
12/31/2017​
[/td][td]YTD[/td][/tr]


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


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3
[/td][td]Austin[/td][td]
42126.12​
[/td][td]
64865.8​
[/td][td]
64,865.80​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4
[/td][td]Boston[/td][td]
106801.43​
[/td][td]
74472.71​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5
[/td][td]Chicago[/td][td]
4081.52​
[/td][td]
2460.36​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6
[/td][td]Detroit[/td][td]
49211.33​
[/td][td]
0​
[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7
[/td][td]Dallas[/td][td]
43219.26​
[/td][td]
0​
[/td][td][/td][/tr]
[/table]
[Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
 
Upvote 0
How about

Excel 2013/2016
ABCD
131/12/201831/12/2017YTD
2
3Austin42126.1264865.8797116.8
4Boston106801.4374472.711211551
5Chicago4081.522460.3646084.75
6Detroit49211.33049562.68
7Dallas43219.26043219.26
Sheet2
Cell Formulas
RangeFormula
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))
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,333
Members
452,636
Latest member
laura12345

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