SUMPRODUCT using Wildcards?

[Tired]

I know this is probably a silly and easy thing, but I'm
tired and running out of time.

If I wanted to do a SUMPRODUCT with a match that was close
enough to what I want (like "Bugs" or "Bugs Bunny") how
would syntactically render it in the =SUMPRODUCT
((a1:a5, "Bugs")*(b1:b5, "foo")).

Thanks much!

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(ISNUMBER(FIND("Bugs",a1:a5))*(b1:b5, "foo"))

 

[Dave R.]

I think you mean to have B1:B5="foo" ... not B1:B5,"foo" ??

=SUMPRODUCT(ISNUMBER(FIND("Bugs",a1:a5))*(b1:b5="foo"))

or

=SUMPRODUCT(ISNUMBER(SEARCH("Bugs",A1:A5))*SEARCH(B1:B5,"foo"))

 

[Don Guillett]

Another if bugs is ALWAYS the 1st four letters

=SUMPRODUCT((LEFT(D516,4)="Bugs")*(b5:b16="foo"))
or only bugs
=SUMPRODUCT((LEFT(D516,4)="Bugs")*1)

 

[Frank Kabel]

Dave
thanks for the correction

 

[Guest]

Yes,


I noticed that I wrote it down wrong a few moments later.
Many thanks for your help. I can go home now.

 

[Harlan Grove]

or only bugs
=SUMPRODUCT((LEFT(D516,4)="Bugs")*1)

....

Why not =COUNTIF(D516,"Bugs*") ?

 

[Don Guillett]

why not.