sumif / sumproduct 2 columns match a criteria

[FDDavis]

I have a sales spread sheet and would like to sum the price column C if
column A is salesman "joe" and column B contains the word "drill" note
that column B will always contain the word drill and other text. see
small sample below

yearly sales
A B C
sales person device and brand price
joe drill Sears $55.99
james saw makita $75.39
mary saw sears $88.32
joe drill Milwakee $98.19
joe saw Milwakee $39.95
joe drill ryobi $129.99
james drill roybi $75.98

Total joe Drills formula =sumproduct((a2:a8="joe")*(b2:b8="drill"
&"*")*(c2:c8))http://www.excelforum.com/newthread.php?s=&action=newthread&forumid=5#

results is $0.00 if I use the complete text in column B then it works
it appears to be a problem using a wildcard for part of the text in
column B

I'm sure there is a simple answer But I don't know it.
Thanks in Advance Frank

 

[Frank Kabel]

Hi
one way:
=sumproduct((a2:a8="joe")*(NOT(ISERROR(FIND("drill",b2:b8))))*(c2:c8))
or
=sumproduct((a2:a8="joe")*(LEN(B2:B8)-LEN(SUBSTITUTE(B2:B8,"drill",""))

 

[Roger Govier]

Hi Frank

You are almost there
=sumproduct((a2:a8="joe")*(isnumber(find("drill",b2:b8)))*(c2:c8))

 

[JE McGimpsey]

One way:

Assume J1 contains the sales person's name ("joe", "james") and J2
contains the part name ("drill", "Sears")

=SUMPRODUCT(--(A1:A100=J1),--(NOT(ISERR(SEARCH("*"&J2&"*",B1:B100)))),C1:
C100)

 

[FDDavis]

I thank you both that did what I was looking for:cool