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"),(D14))

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?

See if this does what you want.

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

HTH,
Paul



"Tevuna" <(E-Mail Removed)> wrote in message
news:147FCF9A-5CF7-4942-B902-(E-Mail Removed)...
> 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?

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?

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

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"),(D14))
>
> 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?

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"),(D14))
> >
> > 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?

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?

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?

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?