Using SUMPRODUCT and wildcard ?

Pauljj

Well-known Member
Joined
Mar 28, 2004
Messages
2,013
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 ?
 

Some videos you may like

Excel Facts

Add Bullets to Range
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
Joined
Mar 28, 2004
Messages
2,013
Please ignore, I have worked out how to do this, apologies
 

Watch MrExcel Video

Forum statistics

Threads
1,118,971
Messages
5,575,300
Members
412,653
Latest member
JyothiGrace
Top