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

H

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
61
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)))
 

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.
Sort by date Sort by votes
Check that your dates in J & K are real dates & not text.
 
Upvote 0
Fluff said:
Check that your dates in J & K are real dates & not text.
Click to expand...
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?
 
Upvote 0
Change the cell format does not change the value in the cells. If you format those columns as General what do you see?
 
Upvote 0
Fluff said:
Change the cell format does not change the value in the cells. If you format those columns as General what do you see?
Click to expand...
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)))
 
Upvote 0
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)))
 
Upvote 0
Solution
Fluff said:
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)))
Click to expand...
This does it. I've never used the FILTER function before - this is fantastic! Thanks for your guidance :)
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Help with difference between 2 dates including am/pm and check multiple entries to see if they occur at the same date and am/pm
Replies
1
Views
514
XLfriend777
XLfriend777
W
  • Question Question
Count periods between to dates/times (lookup)
Replies
0
Views
460
Woofy_McWoof_Woof
W
S
  • Solved
Trouble with INDEX/MATCH for Multiple Criteria
Replies
6
Views
783
Fluff
Fluff
S
  • Solved
Index and match not working over multiple rows
Replies
3
Views
557
Fluff
Fluff
Red over White
  • Solved
The same INDEX/MATCH formual is giving different results, and I can't work out why
Replies
3
Views
706
Fluff
Fluff

Forum statistics

Threads
1,223,907
Messages
6,175,301
Members
452,633
Latest member
DougMo

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