View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Luke M Luke M is offline
external usenet poster
 
Posts: 2,722
Default SUMIF function being used properly?

Sumproduct is what you are looking for.

Example to find quantity of group" 001", product code "A"

=SUMPRODUCT((A2:A5="001")*(C2:C5="A")*(D2:D5))

Note that this will work if your group code is being entered as text. If its
actually a number(value) just formatted to have the leading zeros, remove the
quotation marks and change to just (A2:A5=1).

Or you could make it reference a cell, if you want to have a little more
control over what summation you want.

Your example has no information about weeks, so I cannot give you an
formula. But, if you include another column with week number, you could
simplay add that into the sumproduct equation. Hopefully it gives you some
ideas to the right way to go.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"frenchtoast" wrote:

I am trying to pull data from one file to another.

For simplicity, my data includes multiple customers and multiple products:

Cust. group #/ Cust. Name / Product code / Quantity
001 / Bob / A / 10
001 / Bob / B / 5
001 / Bob / C / 20
001 / Ted / A / 35
001 / Ted / B / 30
001 / Ted / C / 15

I need to look up using a customer group number (contains multiple customers
belonging to one group), and also look up by product code, to provide the
total quantity of that product.

I am using the SUMIF function but for some reason it is returning only one
figure for everything.

There is another trick to this equation, I need to separate this by weeks
(ie. week 1, and week 2 --though I can separate this into separate worksheets
for simplicity - but an extra step).

Thank you.