Using SUMPRODUCT and wildcard ?

[Pauljj]

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

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

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 ?

 

[Pauljj]

Please ignore, I have worked out how to do this, apologies