Combine SUMIF and SUMPRODUCT

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is how the data is setup:

A B C
Type Qt. Rate
Category1 5 $10.00
Category2 6 $2.00
Category1 .5 $.50
Category2 10 $7.00

Here is the objective:

Calculate total amount, by mulitplying Qt. and Rate, if Category = category1
SUMPRODUCT doesn't take an argument for condition, and SUMIF can't multiply.
Is there a formula that combines them both?
 
Why not do your multiplication on the worksheet in d1 and then use:-

=SUMPRODUCT(--(A1:A4="Category 1"),(D1:D4))

Mike
 
Or if you want the multiplication at formula level try:-

=SUMPRODUCT(--(A1:A4="Category 1"),(B1:B4)*(C1:C4))

Mike
 
1) My question is if Excel provides a way to do it all in a single cell.
2) When creating a new porduct column, column D, the SUMIF rather then
SUMPRODUCT should be used. I'm I missing something here?
3) What are the double negative signs (--) that you and many other folks
here are using?
 
Toppers,
Your formula works, I can't understand the syntax, though.
1) SUMPRODUCT multiplies corresponding rows in multiple arrays. If only one
array is given, SUMPRODUCT multiplies all the rows within that column. If it
has no comma, it is only one array.
2) ($A$2:$A$5="Category1") is logical, True or False, so when
*($B$2:$B$5*$C$2:$C$5)
What is being multiplied?
 
SUMPRODUCT builds a multi-dimensional array:

So for your example a2=TRUE,a3=FALSE,a4=TRUE and a5=FALSE. These are
converted to 1 (TRUE) and 0 (FALSE) either by preceding with -- OR *
(Multiplication)

It then multiples a2*b2*c2, a3*b3*c3 etc: the FALSE value (0) will give a
result of 0 and the TRUE values will give 1*b2*c2 (for example).These are
SUMmed to give the result.

See here for a full explanation of SUMPRODUCT:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

HTH
 
Back
Top