Sumproduct help please!

[Thommo]

I have used sumproduct in the past but not with wildcards, not sure if it
possible..but..here is what I am trying to get:

Data table with Product and location joined and the a qty to sum eg in A1:
Sydney - Dry Starch 20
Sydney - Wet Starch 10
Sydney - Dry Gluten 20
Melbourne - Dry Starch 50
Melbourne - Wet Starch 10

My issue is that I want to sum the Site and General Product "*Starch"
"*Gluten"
to end up with in an area say F1, F2 etc
Sydney - *Starch = 30
Sydney - *Gluten = 20
Melbourne - *Starch = 60

I have each part of sumproduct working but not together.
Here is my attempt:
=Sumproduct(--(Match(A1:A5 = F1)),--(B1:B5))
I keep getting errors??

 

[Pete_UK]

I suggest you put the town in F1:F3 and the products in G1:G3, then in
H1 you can put this formula:

=SUMPRODUCT((ISNUMBER(SEARCH(F1,A$1:A$5)))*(ISNUMBER(SEARCH(G1,A$1:A
$5))),B$1:B$5)

or if you prefer:

=SUMPRODUCT(--(ISNUMBER(SEARCH(F1,A$1:A$5))),--(ISNUMBER(SEARCH(G1,A
$1:A$5))),B$1:B$5)

Then copy down to H3 to give you this:

Sydney Starch 30
Sydney Gluten 20
Melbourne Starch 60

You can't use wildcards in the way you were trying with SP - you use
SEARCH (or FIND, if case is important) to see if the word is present.

Hope ths helps.

Pete