multiple conditions for sumif

  • Thread starter Thread starter samilator
  • Start date Start date
S

samilator

hello.

can anyone help me with this?

A B C D
1 stock no. size color pcs.
2 6075 S white 60
3 6075 M blue 24
4 6075 L yellow 12
5 6075 S white 24
6 6078 M white 36
7 6079 XL green 12
8 6078 M green 48

these are daily inventory entries. i tried to use
{=SUM(IF((A:A="6075")+(B:B="S")+(C:C="white"),D:D,0))} to get the
number of pieces that are stock no. 6075, size s, and color white. i
also tried lots of different variations adding AND, OR, but i always
get errors or i don't get the correct total. can anyone please help?
thanks!!!
 
Hi samilator!

The table looks just fine in CDO!

Try this:

=SUMPRODUCT(--(A3:A9=6075),--(B3:B9="S"),--
(C3:C9="WHITE"),D3:D9)

Or, to make this more versatile, use cell references in
place of the hard coded values. Use cells A1, B1 and C1 as
your "lookup" criteria:

A1 = stock number = 6075
B1 = size = S
C1 = color = white

=SUMPRODUCT(--(A3:A9=A1),--(B3:B9=B1),--(C3:C9=C1),D3:D9)

Biff
 
Sam,

Your formula should sum just fine, but you need to pay attention to a few
things in your formula. First, with the + sign you're doing an OR summation
not AND summation. Second, you're doing the whole column.

Instead, create dynamnic names for each interval. So if we call the names as

1) StockNo
2) Size
3) Color
4) Pcs

The formula for the dynamic name for StockNo is:

=OFFSET(Sheet1!$A$2,0,0,CONTA(Sheet1!$A:$A)-1,1)

This will return an named-array. I assume the header is on A1 and the first
code on A2. Now, all you need to do is to repeat the process for each of the
other ranges.

{=SUM(IF((StockNo="6075")+(Size="S")+(Color="white"),Pcs,0))}

You can also create names for the criteria and feed them to the formula
instead of hardcoding them into it:

{=SUM(IF((StockNo=Crit1)+(Size=Crit2)+(Color=Crit3),Pcs,0))}

Robert
 
Did I waste my time?
-----Original Message-----
Hi samilator!

The table looks just fine in CDO!

Try this:

=SUMPRODUCT(--(A3:A9=6075),--(B3:B9="S"),--
(C3:C9="WHITE"),D3:D9)

Or, to make this more versatile, use cell references in
place of the hard coded values. Use cells A1, B1 and C1 as
your "lookup" criteria:

A1 = stock number = 6075
B1 = size = S
C1 = color = white

=SUMPRODUCT(--(A3:A9=A1),--(B3:B9=B1),--(C3:C9=C1),D3:D9)

Biff

.
Click to expand...
 
Back
Top