SUMIF Question

Posted by Juan Carlos on November 19, 2001 8:08 AM

Can someone please help me with the SUMIF Function:

I have 3 columns: (Product - Warehouse - QTY )

Products could be any range of items.
Warehouse is any of 3 locations
Qty is Quantity of each product.

The problem is that I need to sum the Qty for a particular item in a particular warehouse and I cant get sumif to distinguish one particular warehouse.

PS. I know this can be done with VLOOKUP, but I prefer to use SUMIF because it allows for Wildcards when searching.

Thanks

J.C.

Posted by Aladin Akyurek on November 19, 2001 8:23 AM

SUMIF cannot handle more than one condition.

I'd suggest using Pivot tables for this task.

Otherwise, build a list of products in a column, say, E from E2 on and a list of warehouses in row 1 from F1 on.

In F2 enter: =SUMPRODUCT(($A$1:$A$100=$E2)*($B$1:$B$100=F$1),($C$1:$C$100))

Copy this across then down. It will give you the desired totals.

I assumed the data to be in A1:C100, where A1:A100 houses products, B1:B100 warehouses, and C1:C100 associated quantities.

Aladin

Posted by Mark W. on November 19, 2001 8:25 AM

{=SUM(('Product'="ABC")*('Warehouse'=1)*'Qty')}

Note: This is an array formula which must be
entered using the Control+Shift+Enter key
combination. The outermost braces, {}, are not
entered by you -- they're supplied by Excel in
recognition of a properly entered array formula.

Posted by Juan Carlos on November 19, 2001 9:21 AM

I'd suggest using Pivot tables for this task. Otherwise, build a list of products in a column, say, E from E2 on and a list of warehouses in row 1 from F1 on. In F2 enter: =SUMPRODUCT(($A$1:$A$100=$E2)*($B$1:$B$100=F$1),($C$1:$C$100)) Copy this across then down. It will give you the desired totals. I assumed the data to be in A1:C100, where A1:A100 houses products, B1:B100 warehouses, and C1:C100 associated quantities. Aladin

Thanks Mark and Aladin, both formulas worked but they lack the wildcard capability. Any suggestions.

Posted by Aladin Akyurek on November 19, 2001 9:30 AM

=SUMPRODUCT((ISNUMBER(SEARCH("ur",B1:B3)))*(A1:A3="anb"),C1:C3)

is an example how you can use wild card capability.

Strings like "?ur" or "*ur*" within SEARCH will work.

Aladin

Posted by Mark W. on November 19, 2001 10:25 AM

{=SUM(ISNUMBER(MATCH("*ap*",Product,0))*(Warehouse=1)*Qty)} : SUMIF cannot handle more than one condition. : I'd suggest using Pivot tables for this task. : Otherwise, build a list of products in a column, say, E from E2 on and a list of warehouses in row 1 from F1 on. : In F2 enter: =SUMPRODUCT(($A$1:$A$100=$E2)*($B$1:$B$100=F$1),($C$1:$C$100)) : Copy this across then down. It will give you the desired totals. : I assumed the data to be in A1:C100, where A1:A100 houses products, B1:B100 warehouses, and C1:C100 associated quantities. : Aladin :