Morning all, I am using this formula below which works perfectly...however
$G7:G1000 contains time ranges, for example it could say 0900-1000 or 1000-1100 and so on
In reference T11, T12, T13 and so on, I have a list of all those time ranges, so the below code works without an issue
The problem I have is that some of the text in G7:G1000 says, for example
0900-1000 H
1000-1100
0800-0900
1700-1800 H
So as you can see some of the times, are suffixed with a space and letter H
What I had hope to do was simply change the code to this
Simply adding in a wildcard asterix to the T11 but as you cleverer people will have realised I can't use a wildcard within SUMPRODUCT
Can anyone think of an alternative way of achieving this please ?
$G7:G1000 contains time ranges, for example it could say 0900-1000 or 1000-1100 and so on
In reference T11, T12, T13 and so on, I have a list of all those time ranges, so the below code works without an issue
Code:
SUMPRODUCT((Schedule!G$7:G$1000=$T11)*SUBTOTAL(103,OFFSET(Schedule!G$7,ROW(Schedule!G$7:G$1000)-MIN(ROW(Schedule!G$7:G$1000))
The problem I have is that some of the text in G7:G1000 says, for example
0900-1000 H
1000-1100
0800-0900
1700-1800 H
So as you can see some of the times, are suffixed with a space and letter H
What I had hope to do was simply change the code to this
Code:
SUMPRODUCT((Schedule!G$7:G$1000=$T11*)*SUBTOTAL(103,OFFSET(Schedule!G$7,ROW(Schedule!G$7:G$1000)-MIN(ROW(Schedule!G$7:G$1000))
Simply adding in a wildcard asterix to the T11 but as you cleverer people will have realised I can't use a wildcard within SUMPRODUCT
Can anyone think of an alternative way of achieving this please ?