Combine SUMIF and SUMPRODUCT
 

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?

Answer: Combine SUMIF and SUMPRODUCT
 

Yes, you can use a combination of SUMIF and SUMPRODUCT to achieve your objective. Here's how:

1. First, create a helper column (let's say column D) where you will multiply the Qt. and Rate for each row. In cell D2, enter the formula =B2*C2 and drag it down to apply the formula to all rows.
2. Next, use the SUMIF function to sum the values in column D where the Type in column A is "Category1". In a cell, enter the formula =SUMIF(A:A,"Category1",D:D).
3. Finally, wrap the SUMIF function inside the SUMPRODUCT function to get the total amount. In a cell, enter the formula =SUMPRODUCT(SUMIF(A:A,"Category1",D:D)).

This formula will multiply the Qt. and Rate for each row where the Type is "Category1", sum the results, and return the total amount. You can adjust the criteria in the SUMIF function to calculate the total amount for other categories as well.

Combine SUMIF and SUMPRODUCT
 

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

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

Mike

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

Combine SUMIF and SUMPRODUCT
 

See if this does what you want.

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

HTH,
Paul



"Tevuna" wrote in message
...
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?

Combine SUMIF and SUMPRODUCT
 

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

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

Mike

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

Combine SUMIF and SUMPRODUCT
 

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

Combine SUMIF and SUMPRODUCT
 

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?

"Mike H" wrote:

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

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

Mike

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

Combine SUMIF and SUMPRODUCT
 

The -- coerces Excel into treating text as a number

Mike

"Tevuna" wrote:

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?

"Mike H" wrote:

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

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

Mike

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

Combine SUMIF and SUMPRODUCT
 

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?

Combine SUMIF and SUMPRODUCT
 

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?

Combine SUMIF and SUMPRODUCT
 

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

"Toppers" wrote:

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?