SUMPRODUCT without exact match?

[danielleissy]

Hi,

So far this sum works OK, it looks up if the part stored in N4 has been used in col F, then checks if the vehicle type stored in N2 matches any used in column E. It then draws the qty from col D.

However I can only get it to check the vehicle type if its an exact match. But my list in col E has more than just the one word. For example cell N2 might say "Sprinter" but col E might have "Mercedes Sprinter 2006 - 2012" so it doesn't pick it up.

=SUMPRODUCT(--($F$3:$F$13=N4), --($E$3:E$13=$N$2), $D$3:$D$13)

Then I also need it to deduct the quantity being picked up, from the existing stock figure, which is column O.
Any help would be greatly appreciated.

 

[joeu2004]

=SUMPRODUCT(--($F$3:$F$13=N4), --ISNUMBER(SEARCH($N$2,E$3:E$13)), $D$3:$D$13)

and

=SUMPRODUCT(--($F$3:$F$13=N4), --ISNUMBER(SEARCH($N$2,E$3:E$13)), $D$3:$D$13 - $O$3:$O$13)

Caveat: If the value of N2 is a common word, this might inadventent include other things. For example, if N2 is "print", the search will match "x imprint y" as well as "a print b". Your intent might be to include only the latter. It is difficult to exclude the first in Excel; easier to do in VBA. ("Difficult", but not impossible.) Hopefully, you are confident that the problem will not arise.