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.
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.
Would not it be sufficient to write two separate SUMPRODUCT functions and sum-up their results? =SUMPRODUCT(....)+SUMPRODUCT(...)