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

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
2TYPEINDEXSELLCPTRADERDATESTARTENDPRODZONEZONEQTYPRCENTEREDMODIFIED (MWH)(MWH)123456789101112131415161718192021222324DayPeakDateMonthDayHEERCT-NERCT-SERCT-WERCT-HERCT-ALL
3PhysicalRealTimeMarketBuy-7/1/20217/31/20217x24ERCOT_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 2020Dec11#N/A
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
9PhysicalRealTimeMarketBuy6/17/20207/1/20206/30/20227x16ERCOT_HERCT-H6/17/20206/17/20200.00.00.00.00.00.01.01.01.01.01.01.01.01.01.01.01.01.01.01.01.01.00.00.0WDOFF12/01/2020Dec 2020Dec17
10PhysicalRealTimeMarketBuy6/17/20208/1/20207/31/20237x16ERCOT_HERCT-H6/17/20206/17/20200.00.00.00.00.00.02.02.02.02.02.02.02.02.02.02.02.02.02.02.02.02.00.00.0WDOFF12/01/2020Dec 2020Dec18
11PhysicalRealTimeMarketBuy6/24/20209/1/20209/30/20207x16ERCOT_HERCT-H6/24/20206/24/20200.00.00.00.00.00.04.04.04.04.04.04.04.04.04.04.04.04.04.04.04.04.00.00.0WDOFF12/01/2020Dec 2020Dec19
12PhysicalRealTimeMarketBuy6/24/20209/1/20209/30/20207x8ERCOT_HERCT-H6/24/20206/24/20201.51.51.51.51.51.50.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.01.51.5WDOFF12/01/2020Dec 2020Dec110
13PhysicalRealTimeMarketBuy6/24/202010/1/202010/31/20207x16ERCOT_HERCT-H6/24/20206/24/20200.00.00.00.00.00.02.02.02.02.02.02.02.02.02.02.02.02.02.02.02.02.00.00.0WDOFF12/01/2020Dec 2020Dec111
14PhysicalRealTimeMarketBuy6/24/202010/1/202010/31/20207x8ERCOT_HERCT-H6/24/20206/24/20201.01.01.01.01.01.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.01.01.0WDOFF12/01/2020Dec 2020Dec112
15PhysicalRealTimeMarketBuy6/24/202011/1/20205/31/20217x16ERCOT_HERCT-H6/24/20206/24/20200.00.00.00.00.00.01.51.51.51.51.51.51.51.51.51.51.51.51.51.51.51.50.00.0WDOFF12/01/2020Dec 2020Dec113
16PhysicalRealTimeMarketBuy6/24/202011/1/20205/31/20217x8ERCOT_HERCT-H6/24/20206/24/20201.01.01.01.01.01.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.01.01.0WDOFF12/01/2020Dec 2020Dec114
17PhysicalRealTimeMarketBuy6/24/20206/1/20216/30/20217x16ERCOT_HERCT-H6/24/20206/24/20200.00.00.00.00.00.01.01.01.01.01.01.01.01.01.01.01.01.01.01.01.01.00.00.0WDOFF12/01/2020Dec 2020Dec115
18PhysicalRealTimeMarketBuy6/24/20206/1/20216/30/20217x8ERCOT_HERCT-H6/24/20206/24/20200.50.50.50.50.50.50.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.50.5WDOFF12/01/2020Dec 2020Dec116
19PhysicalRealTimeMarketBuy6/24/20207/1/20217/31/20217x24ERCOT_HERCT-H6/24/20206/24/20200.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.5WDOFF12/01/2020Dec 2020Dec117
20PhysicalRealTimeMarketBuy6/24/20209/1/20209/30/20207x16ERCOT_NERCT-N6/24/20206/24/20200.00.00.00.00.00.03.03.03.03.03.03.03.03.03.03.03.03.03.03.03.03.00.00.0WDOFF12/01/2020Dec 2020Dec118
21PhysicalRealTimeMarketBuy6/24/20209/1/20209/30/20207x8ERCOT_NERCT-N6/24/20206/24/20201.51.51.51.51.51.50.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.01.51.5WDOFF12/01/2020Dec 2020Dec119
22PhysicalRealTimeMarketBuy6/24/202010/1/20201/31/20217x16ERCOT_NERCT-N6/24/20206/24/20200.00.00.00.00.00.01.51.51.51.51.51.51.51.51.51.51.51.51.51.51.51.50.00.0WDOFF12/01/2020Dec 2020Dec120
23PhysicalRealTimeMarketBuy6/24/202010/1/20201/31/20217x8ERCOT_NERCT-N6/24/20206/24/20201.01.01.01.01.01.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.01.01.0WDOFF12/01/2020Dec 2020Dec121
24PhysicalRealTimeMarketBuy6/24/20202/1/20212/28/20215x16ERCOT_NERCT-N6/24/20206/24/20200.00.00.00.00.00.01.51.51.51.51.51.51.51.51.51.51.51.51.51.51.51.50.00.0WDOFF12/01/2020Dec 2020Dec122
25PhysicalRealTimeMarketBuy6/24/20202/1/20212/28/20217x8ERCOT_NERCT-N6/24/20206/24/20201.01.01.01.01.01.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.00.01.01.0WDOFF12/01/2020Dec 2020Dec123
26PhysicalRealTimeMarketBuy6/24/20202/1/20212/28/20212x16ERCOT_NERCT-N6/24/20206/24/20200.00.00.00.00.00.01.01.01.01.01.01.01.01.01.01.01.01.01.01.01.01.00.00.0WDOFF12/01/2020Dec 2020Dec124
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)))
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
Check that your dates in J & K are real dates & not text.
 

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
1BUY/TRADETRADEHEDGETOTALDAILYHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHEHedges (Gross)
2TYPEINDEXSELLCPTRADERDATESTARTENDPRODZONEZONEQTYPRCENTEREDMODIFIED (MWH)(MWH)1.002.003.004.005.006.007.008.009.0010.0011.0012.0013.0014.0015.0016.0017.0018.0019.0020.0021.0022.0023.0024.00DayPeakDateMonthDayHEERCT-NERCT-SERCT-WERCT-HERCT-ALL
3PhysicalRealTimeMarketBuy-44378444087x24ERCOT_NERCT-N--000000000000000000000000WDOFF44166Dec 2020Dec11.000
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
9PhysicalRealTimeMarketBuy4399944013447427x16ERCOT_HERCT-H4399943999000000111111111111111100WDOFF44166Dec 2020Dec17.00
10PhysicalRealTimeMarketBuy4399944044451387x16ERCOT_HERCT-H4399943999000000222222222222222200WDOFF44166Dec 2020Dec18.00
11PhysicalRealTimeMarketBuy4400644075441047x16ERCOT_HERCT-H4400644006000000444444444444444400WDOFF44166Dec 2020Dec19.00
12PhysicalRealTimeMarketBuy4400644075441047x8ERCOT_HERCT-H44006440061.51.51.51.51.51.500000000000000001.51.5WDOFF44166Dec 2020Dec110.00
13PhysicalRealTimeMarketBuy4400644105441357x16ERCOT_HERCT-H4400644006000000222222222222222200WDOFF44166Dec 2020Dec111.00
14PhysicalRealTimeMarketBuy4400644105441357x8ERCOT_HERCT-H4400644006111111000000000000000011WDOFF44166Dec 2020Dec112.00
15PhysicalRealTimeMarketBuy4400644136443477x16ERCOT_HERCT-H44006440060000001.51.51.51.51.51.51.51.51.51.51.51.51.51.51.51.500WDOFF44166Dec 2020Dec113.00
16PhysicalRealTimeMarketBuy4400644136443477x8ERCOT_HERCT-H4400644006111111000000000000000011WDOFF44166Dec 2020Dec114.00
17PhysicalRealTimeMarketBuy4400644348443777x16ERCOT_HERCT-H4400644006000000111111111111111100WDOFF44166Dec 2020Dec115.00
18PhysicalRealTimeMarketBuy4400644348443777x8ERCOT_HERCT-H44006440060.50.50.50.50.50.500000000000000000.50.5WDOFF44166Dec 2020Dec116.00
19PhysicalRealTimeMarketBuy4400644378444087x24ERCOT_HERCT-H44006440060.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.50.5WDOFF44166Dec 2020Dec117.00
20PhysicalRealTimeMarketBuy4400644075441047x16ERCOT_NERCT-N4400644006000000333333333333333300WDOFF44166Dec 2020Dec118.00
21PhysicalRealTimeMarketBuy4400644075441967x8ERCOT_NERCT-N44006440061.51.51.51.51.51.500000000000000001.51.5WDOFF44166Dec 2020Dec119.00
22PhysicalRealTimeMarketBuy4400644105442277x16ERCOT_NERCT-N44006440060000001.51.51.51.51.51.51.51.51.51.51.51.51.51.51.51.500WDOFF44166Dec 2020Dec120.00
23PhysicalRealTimeMarketBuy4400644105442277x8ERCOT_NERCT-N4400644006111111000000000000000011WDOFF44166Dec 2020Dec121.00
24PhysicalRealTimeMarketBuy4400644228442555x16ERCOT_NERCT-N44006440060000001.51.51.51.51.51.51.51.51.51.51.51.51.51.51.51.500WDOFF44166Dec 2020Dec122.00
25PhysicalRealTimeMarketBuy4400644228442557x8ERCOT_NERCT-N4400644006111111000000000000000011WDOFF44166Dec 2020Dec123.00
26PhysicalRealTimeMarketBuy4400644228442552x16ERCOT_NERCT-N4400644006000000111111111111111100WDOFF44166Dec 2020Dec124.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
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
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)))
 
Solution

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
52
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
55,358
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top