The INDEX(…MATCH(…)) function combination is very useful in SC spreadsheet calculations. It allows you to find values within defined ranges, and in SC’s descending-order spreadsheets. Here is an example:
Volume sum between a start time and an end time of the current day.
Put this in cell Y3 (or in any column O to Z…BR): =IF(INT(A3)>INT(A4),Y4+1,Y4) This increments the day count.
Put this in cell Y1: =MAX(Y3:Y1002) This finds the current day in the day count.
Put this in cell Z3 (or in any column O to Z…BR): =IF(Y3=$Y$1,FRACTIME(A3),0) This will extract the time of day of only the current day from the datetime of each bar in column A
Put this in cell H3 (or in any static cell): 08:35:00 This is the start time of your summing window.
Put this in cell H4 (or in any static cell): 09:14:00 This is the end time of your summing window.
Note: The time in H3 must be earlier than the time in H4.
Put this in cell H5 (or in any static cell): =SUM(INDEX(F$3:F$1002,MATCH($H$4,Z$3:Z$1002,-1),1):INDEX(F$3:F$1002,MATCH($H$3,Z$3:Z$1002,-1),1)) This is the sum of the volume between the start and end times of your summing window (assuming you have the default 1000 rows in your worksheet).
