Search code examples
excelexcel-formulaexcel-2013

How to properly use OR within SUMPRODUCT function


I am trying to program a cell to calculate how many alerts happen within a certain trading session. I am using a 24 hour clock. However, the trading sessions don't line up evenly with the clock.

  • London (02:00:00 - 06:59:59) (COLOR CODED DK. BLUE)
  • AFTER Hours 1 (16:00:00 - 11:59:59) (COMBINING BOTH AFTER HOURS SESSIONS AND COLOR CODED LT. BLUE)
  • AFTER Hours 2 (00:00:01 - 01:59:59) (COMBINING BOTH AFTER HOURS SESSIONS AND COLOR CODED LT. BLUE) (These are represented within my spreadsheet. For instance, London is AE5 and AF5.)

The difficulty has come in programming the AFTER Hours sessions calculation. For the other sessions, I simply used =SUMPRODUCT(--($E$11:$E$61>=AE5),--($E$11:$E$61<=AF5)) for London where the E11:E61 represents the inputted alert times. No troubles at all here.

Because of the AFTER Hours having two separate data entries, I thought an OR statement would be correct within a SUMPRODUCT function. AFTER HOURS 1 is represented by AE8 and AF8, while AFTER HOURS 2 is represented by AE9 and AF9. Here is the SUMPRODUCT function that I have tried to use.

=SUMPRODUCT(--($E11:$E61>=$AE$8),--($E11:$E61<=$AF$8)+--($E11:$E61>=$AE$9),--($E11:$E61>=$AF$9))

It should be returning a 1, but comes up with a 0. Not sure if I am missing something in the syntax or improperly using the + for the OR.


Solution

  • Would not it be sufficient to write two separate SUMPRODUCT functions and sum-up their results? =SUMPRODUCT(....)+SUMPRODUCT(...)