Combine SUMIF and SUMPRODUCT

[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?

 

[Guest]

Why not do your multiplication on the worksheet in d1 and then use:-

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

Mike

 

[PCLIVE]

See if this does what you want.

=SUMPRODUCT(--(A2:A5="Category1"),B2:B5*C2:C5)

HTH,
Paul

 

[Guest]

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

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

Mike

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

The -- coerces Excel into treating text as a number

Mike

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

I Thank you so much, you realy are at the top of the toppers