# Using SUMPRODUCT and wildcard ?

#### Pauljj

##### Well-known Member
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

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 ?

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

#### Pauljj

##### Well-known Member
Please ignore, I have worked out how to do this, apologies

Replies
6
Views
136
Replies
2
Views
126
Replies
0
Views
124
Replies
9
Views
317
Replies
2
Views
229