SUMIF w/ 2 Criteria

  • Thread starter Thread starter carl
  • Start date Start date
C

carl

My data is organized like this:

ColA ColB ColC
ProductA 10 Shipped
ProductA 20 Not-Shipped
ProductB 30 Shipped
ProductC 40 Shipped

I would like to SUMIF Product A and Shipped.

Thanks In Advance.
 
Hi

One way is to use SUMPRODUCT()

=SUMPRODUCT((A2:A5="ProductA")*(C2:C5="Shipped")*(B2:B5))

Andy.
 
=SUMPRODUCT(--(A2:A50="ProductA"),--(C2:C50="Shipped"),B2:B50)

Replace the hard coded criteria with cell references so you can switch
criteria easier

=SUMPRODUCT(--(A2:A50=D2),--(C2:C50=E2),B2:B50)
 
Hi Carl!

SUMIF will not accept multiple criteria so one way is to use
SUMPRODUCT:

=SUMPRODUCT(($A$1:$A$4="ProductA")*($C$1:$C$4="Shipped")*($B$1:$B$4))

The first two bracketed expressions are implicit IF functions that
return TRUE or FALSE. Multiplying them together coerces them to values
of 1 for TRUE and 0 for FALSE. Only if both are TRUE will you get
1*1*corresponding value in B.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
You have to use an array formula. Type the following into
the cell that should display the results:

=SUM(IF(A2:A5="ProductA",1,0)*IF(C2:C5="shipped",1,0)
*B2:B5)

Instead of pressing ENTER, press SHIFT-CONTROL-ENTER to
enter the formula as an array formula. You should see a
pair of curly braces {} around the formula if you enter
it correctly. The example above assumes your actual data
resides in cells A2:C5.

This formula creates three arrays in memory. The
first "If" clause creates an array of ones and zeroes (a
1 if the entry equals ProductA, a zero if it doesn't.)
The second "If" clause creates a second array of ones and
zeroes, entering a 1 if the value is shipped, a zero if
not shipped. The portion "B2:B5" creates a third array,
consisting of the values in column B of you data. The
corresponding elements of each array are then multiplied
by each other, and the results of the multiplication are
added by the Sum function.

So, for your data in the first row, the first "if"
creates a value of 1 (because it equals ProductA), the
second "if" creates a value of 1 (because it has
shipped), and the "B2:B5 portion creates a value of 10. 1
* 1* 10=10, of course.

All the remaining rows of data will generate a zero in
one of the "if" clauses, because either the product is
not ProductA, or because it is ProductA but hasn't
shipped.

You may think you could use the AND function in the
formula, instead of multiplication. However, the AND
function doesn't give the expected results. Also, in
the "if" functions, you don't really have to include the
zero as the value if the comparison statement is false.
Excel will default to zero. Finally, I have been reading
a book titled Microsoft Excel 2000 formulas, by John
Walkenbach. it has some good examples of array fromulas,
if you're interested. The ISBN for it is 0-7645-4609-0
 
Didn't think about the SUMPRODUCT functin. It does the
same thing as my array formula, but is easier to
understand.
 
Back
Top