# Seeking Guidance! Complex SUM/INDEX/MATCH with multiple AND/OR logic qualifiers including between two dates

#### Hawk11ns

##### Board Regular
I am finding it challenging to nest AND/OR criteria in my SUM/INDEX/MATCH function. My end goal is, for each hour ending "HE" of each day "DAY" sum all the values across the index that meet the following logical criteria

ROWS:
AND( (Start Date is <= DAY, End Date is >= DAY), OR(PROD = 7x24, PROD = 7x8, PROD = 2x8), Hedge zone = 'ERCT-N')

COLUMN:
HE = Index HE

Below is my code and my formula is in cell BA3 and looks like this:

=SUM(INDEX(U3:AR26,MATCH(1,(J3:J26<=AV3)*(K3:K26>=AV3)*((L3:L26="7x24")+(L3:L26="7x8")+(L3:L26="2x8"))*(N3:N26="ERCT-N"),0),MATCH(AZ3,U2:AR2,0)))

Here's the odd thing, when I reduce my formula I get a zero value when I should get a value of 8

=SUM(INDEX(U3:AR26,MATCH(1,((L3:L26="7x24")+(L3:L26="7x8")+(L3:L26="2x8")),0),MATCH(AZ3,U2:AR2,0)))

Executive Dashboard.xlsm
4PhysicalRealTimeMarketSell-11/1/202011/30/20207x24ERCOT_NERCT-N--0.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.0WDOFF12/01/2020Dec 2020Dec12
5PhysicalRealTimeMarketSell-11/1/20204/30/20217x8ERCOT_NERCT-N--0.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.0WDOFF12/01/2020Dec 2020Dec13
6PhysicalRealTimeMarketSell-11/1/20204/30/20217x16ERCOT_NERCT-N--0.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.0WDOFF12/01/2020Dec 2020Dec14
7PhysicalRealTimeMarketSell-11/1/20201/31/20215x16ERCOT_NERCT-N--0.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.0WDOFF12/01/2020Dec 2020Dec15
8PhysicalRealTimeMarketSell-11/1/20208/31/20212x16ERCOT_NERCT-N--0.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.0WDOFF12/01/2020Dec 2020Dec16
Sheet1
Cell Formulas
RangeFormula
BA3BA3=SUM(INDEX(U3:AR98,MATCH(1,(J3:J98<=AV3)*(K3:K98>=AV3)*((L3:L98="7x24")+(L3:L98="7x8")+(L3:L98="2x8"))*(N3:N98="ERCT-N"),0),MATCH(AZ3,U2:AR2,0)))

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### Fluff

##### MrExcel MVP, Moderator
Check that your dates in J & K are real dates & not text.

#### Hawk11ns

##### Board Regular
Check that your dates in J & K are real dates & not text.
Thanks for the advice. I did make sure to format the cells as date. Also, even without the dates, the reduced formula still isn't working. Am I possible putting it together incorrectly?

#### Fluff

##### MrExcel MVP, Moderator
Change the cell format does not change the value in the cells. If you format those columns as General what do you see?

#### Hawk11ns

##### Board Regular

Change the cell format does not change the value in the cells. If you format those columns as General what do you see?
I believe the dates are in proper format. I've also checked that the HE values are numeric and the 'ERCT-N' and '7x' values are all text. Below is a stripped down, paste-special values version. The result returned should be the sum of the highlighted green cells which is 2.5

Book1
4PhysicalRealTimeMarketSell-44136441657x24ERCOT_NERCT-N--000000000000000000000000WDOFF44166Dec 2020Dec12.00
5PhysicalRealTimeMarketSell-44136443167x8ERCOT_NERCT-N--000000000000000000000000WDOFF44166Dec 2020Dec13.00
6PhysicalRealTimeMarketSell-44136443167x16ERCOT_NERCT-N--000000000000000000000000WDOFF44166Dec 2020Dec14.00
7PhysicalRealTimeMarketSell-44136442275x16ERCOT_NERCT-N--000000000000000000000000WDOFF44166Dec 2020Dec15.00
8PhysicalRealTimeMarketSell-44136444392x16ERCOT_NERCT-N--000000000000000000000000WDOFF44166Dec 2020Dec16.00
Sheet1
Cell Formulas
RangeFormula
BA3BA3=SUM(INDEX(U3:AR26,MATCH(1,(J3:J26<=AV3)*(K3:K26>=AV3)*((L3:L26="7x24")+(L3:L26="7x8")+(L3:L26="2x8"))*(N3:N26=BA2),0),MATCH(AZ3,U2:AR2,0)))

#### Fluff

##### MrExcel MVP, Moderator
The Match function will only look at the 1st row that matches the conditions.
Try
Excel Formula:
=SUM(FILTER(FILTER(\$U\$3:\$AR\$26,(\$J\$3:\$J\$26<=AV3)*(\$K\$3:\$K\$26>=AV3)*((\$L\$3:\$L\$26="7x24")+(\$L\$3:\$L\$26="7x8")+(\$L\$3:\$L\$26="2x8"))*(\$N\$3:\$N\$26=BA\$2)),COUNTIF(AZ3,\$U\$2:\$AR\$2)))

#### Hawk11ns

##### Board Regular
The Match function will only look at the 1st row that matches the conditions.
Try
Excel Formula:
=SUM(FILTER(FILTER(\$U\$3:\$AR\$26,(\$J\$3:\$J\$26<=AV3)*(\$K\$3:\$K\$26>=AV3)*((\$L\$3:\$L\$26="7x24")+(\$L\$3:\$L\$26="7x8")+(\$L\$3:\$L\$26="2x8"))*(\$N\$3:\$N\$26=BA\$2)),COUNTIF(AZ3,\$U\$2:\$AR\$2)))
This does it. I've never used the FILTER function before - this is fantastic! Thanks for your guidance

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

1,127,200
Messages
5,623,336
Members
415,968
Latest member
Chabal74

### 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.

### Which adblocker are you using?

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

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