Sum a date range using Index Match for multiple column variables

[stephenpoff]

Hi,

I have a range of data, with dates in column A, and columns of prices based on 4 possible variables. I'm trying to sum the price data, based on a date range for a selected variable using the below INDEX/MATCH formula

=SUMIFS(INDEX(Data!$O$3:$R$10001,MATCH(E11,Data!$A$3:$A$10001,0),MATCH(B11,Data!$G$2:$J$2,0))

Where:
- Data!$O$3:$R$10001 = price per day for each variable
- Data!$A$3:$A$10001 = dates (matched based on what date is in cell E11)
- Data!$G$2:$J$2 = variable 1-4 (matched based on what variable is in cell B11)

I am trying to change the date look up to sum if within a range (between two dates), rather than just return one value based on one date

Any help?
Thanks

 

[cyrilbrd]

hi,
please post a small sample of your data just like so:

	1	2	3	4
02/25/2020	$ 618.72	$ 753.61	$ 663.96	$ 879.79
02/26/2020	$ 423.64	$ 400.07	$ 691.83	$ 811.28
02/27/2020	$ 595.14	$ 847.71	$ 137.28	$ 621.51
02/28/2020	$ 769.22	$ 129.18	$ 761.73	$ 319.86
02/29/2020	$ 595.14	$ 400.07	$ 691.83	$ 879.79

 

[stephenpoff]

Below is a snip of the data table:

And, I'm trying to find (from the below picture) the following: Max Benefit Payable = Sum of column variable A for date range B in the above table. I don't necessarily need to use the formula in the forum post - just some way to sum for a data range, depending on a variable column

 

[cyrilbrd]

Maybe something along the following:

Mrexcel.xlsx
	A	B	C	D	E
1		1	2	3	4
2	02/25/2020	$ 618.72	$ 753.61	$ 663.96	$ 879.79
3	02/26/2020	$ 423.64	$ 400.07	$ 691.83	$ 811.28
4	02/27/2020	$ 595.14	$ 847.71	$ 137.28	$ 621.51
5	02/28/2020	$ 769.22	$ 129.18	$ 761.73	$ 319.86
6	02/29/2020	$ 595.14	$ 400.07	$ 691.83	$ 879.79
7
8
9
10
11	from	02/25/2020
12	to	02/26/2020
13	for	1
14	sum	$ 1,042.36
stephenpoff

Cell Formulas
Range	Formula
B14	B14	=SUM(IF($B$1:$E$1=$B$13,IF($A$2:$A$6>=$B$11,IF($A$2:$A$6<=$B$12,$B$2:$E$6))))

 

[stephenpoff]

Thanks for your help - it is returning zero though, when I try that in my example (below) - what have I got wrong?

 

[cyrilbrd]

Please verify that your dates are dates, no trailing space after the values in row 1 and that your amounts are not texts.
and adjust references so it will catch up the new criteria when copying down:
=SUM(IF($B$1:$E$1=$A11,IF($A$2:$A$6>=$B11,IF($A$2:$A$6<=$C11,$B$2:$E$6))))

 

[stephenpoff]

Yes - checked all that (changed the dates to New Zealand to match my version). Have also tried it with different column labels as well

 

[cyrilbrd]

1. In any blank cell, type the value 1.
2. Make sure the cell in which you typed 1 is formatted as a number.
3. Select the cell with the 1 and Copy.
4. Select the range A1 to E6 .
5. Choose Paste Special.
6. Under Operation , click Multiply and then click OK.

 

[stephenpoff]

Still nothing...