Sumproduct help please!

T

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??
 
P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top