SUMPRODUCT SUMIF wildcard help needed

[twl2009]

I have the below sumproduct formula which looks at another sheet holding sales orders.

=SUMPRODUCT(--('Sales by line'!$J:$J=$E7),--('Sales by line'!$G:$G>=M$4),--('Sales by line'!$G:$G<Q$4),'Sales by line'!$H:$H,'Sales by line'!$K:$K)


Col J matches to a product
Col G is a date
Col H is quantity
Col K is £

So this gives sum of prices paid for a certain product between two dates, so far so simple.

What i need to do next is add one more condition which looks at Col B, which holds order codes.

I need to be able to add a criteria that only shows order codes that start with certain letters.

In SumIf I could have used the wildcard feature &"*", but that doesnt work in sumproduct

I could easily have used the LEFT function except i need to check for different numbers of characters like "WS", "W" or just ""

In a SUMIF formula I would have used the below as range and criteria

,'Sales by line'!$B:$B,$F$1&"*",



I have tried everything I can to get this working, but it is beyond me.



Any help would be amazing
Twl

 

[BarryL]

Hi, Can you try the below?

=SUMPRODUCT(--('Sales by line'!$J:$J=$E7),--('Sales by line'!$G:$G>=M$4),--('Sales by line'!$G:$G<Q$4),--(ISNUMBER(SEARCH($F$1,'Sales by line'!$B:$B))),'Sales by line'!$H:$H,'Sales by line'!$K:$K)

 

[twl2009]

Thank you for your help, thats worked perfectly

 

[BarryL]

Thank you for your help, thats worked perfectly

glad it worked

 

[twl2009]

Bit of a longshot, but the cell F1 contains data validation drop down for the user to change. At the moment the entries would include first characters of an order ID, is there a way I could have something entered in F1 that would indicate everything but the characters entered. Like <>WO or "<>"&"WO" or anything this sort that would exclude this order type from the formula??
Thanks
Twl

 

[BarryL]

does F1 contain all possible order ID starting letters? if so and you want to exclude the one selected you could use:

=SUMPRODUCT(--('Sales by line'!$J:$J=$E7),--('Sales by line'!$G:$G>=M$4),--('Sales by line'!$G:$G<Q$4),--(NOT(ISNUMBER(SEARCH($F$1,'Sales by line'!$B:$B)))),'Sales by line'!$H:$H,'Sales by line'!$K:$K)

 

[twl2009]

Ah wait i could have another cell used when I need to exclusion code and have

=SUMPRODUCT(--('Sales by line'!$J:$J=$E7),--('Sales by line'!$G:$G>=M$4),--('Sales by line'!$G:$G<Q$4),--(ISNUMBER(SEARCH($F$1,'Sales by line'!$B:$B))),--(NOT(ISNUMBER(SEARCH($F$2,'Sales by line'!$B:$B)))),'Sales by line'!$H:$H,'Sales by line'!$K:$K)

would that work?

 

[twl2009]

Brilliant, that has worked a treat, thanks for all your help