Sumif Index Match date range

O

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?
 
Sort by date Sort by votes
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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
Fluff said:
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
Click to expand...

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
You must log in or register to reply here.

Similar threads

P
  • Solved
Dynamic Sheet Tabs in Index Match function
Replies
9
Views
295
Peter_SSs
Peter_SSs
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
298
henrik2h
H
S
  • Question Question
Index Match with multiple conditions including dates
Replies
1
Views
106
StephenCrump
S
R
  • Question Question
Index Match in multiple tabs
Replies
1
Views
114
Domenic
D
S
  • Question Question
How can I exactly count NETWORKDAYS in multiple date ranges, after a specific date?
Replies
1
Views
282
Tetra201
Tetra201

Forum statistics

Threads
1,224,827
Messages
6,181,200
Members
453,022
Latest member
RobertV1609

Share this page

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