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

"Tevuna" wrote:

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?




"Toppers" wrote:

=SUMPRODUCT(($A$2:$A$5="Category1")*($B$2:$B$5*$C$ 2:$C$5))

"Tevuna" wrote:

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?