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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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,812
Messages
5,574,471
Members
412,595
Latest member
slim313
Top