"SUMPRODUCTIF"

[PaladinWhite]

I find myself asking a lot of questions on these boards lately... Thank you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would
do - the catch is that I only want to multiply pairs where the corresponding
cell in Column A contains the text "YELLOW". For instance, in this case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

.... I would want to return [(2*6) + (4*12)] = 60.

 

[Ron Coderre]

Try something like this:

=SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Tyro]

=SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5)

Tyro

 

[PaladinWhite]

That seems to be doing exactly what I needed... What do the two minus signs
(--) preceding that argument do? I did a little searching around, and it
looks like they... multiply by 1... thereby doing something with the format?
I'm still lost!

Try something like this:

=SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I find myself asking a lot of questions on these boards lately... Thank you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would
do - the catch is that I only want to multiply pairs where the
corresponding
cell in Column A contains the text "YELLOW". For instance, in this case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

... I would want to return [(2*6) + (4*12)] = 60.

 

[Ron Coderre]

In this formula
=SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10)

(A1:A10="YELLOW") returns a series of TRUE/FALSE values
which are NOT numeric, so SUMPRODUCT can't use them "as is"

However, in Excel, when TRUE/FALSE values are acted upon by
an arithmetic operator (+, -, *, / ) it converts
TRUE to 1
and
FALSE to 0

We use the Dbl-Minus as an unambiguous way of indicating
that we are forcing a numeric conversion.

It works this way:
TRUE = TRUE
-TRUE = -1
--TRUE = 1

So this series: --(TRUE; FALSE; TRUE; TRUE)
becomes 1; 0; 1; 1

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

That seems to be doing exactly what I needed... What do the two minus
signs
(--) preceding that argument do? I did a little searching around, and it
looks like they... multiply by 1... thereby doing something with the
format?
I'm still lost!

Try something like this:

=SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I find myself asking a lot of questions on these boards lately... Thank
you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT()
would
do - the catch is that I only want to multiply pairs where the
corresponding
cell in Column A contains the text "YELLOW". For instance, in this
case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

... I would want to return [(2*6) + (4*12)] = 60.

 

[Tyro]

The first "-" coerces Excel to change the logical value of TRUE to its
numeric equivalent of 1. But the "-", makes the TRUE -1. The second "-"
makes the -1
a 1. Simple algebra, -(-1) = 1. As for the logical value FALSE which is 0,
the application of "--" has no meaning since 0 is neither positive or
negative and remains 0
In Excel a 0 is FALSE and all other values are TRUE.

Tyro

That seems to be doing exactly what I needed... What do the two minus
signs
(--) preceding that argument do? I did a little searching around, and it
looks like they... multiply by 1... thereby doing something with the
format?
I'm still lost!

Try something like this:

=SUMPRODUCT(--(A1:A10="YELLOW"),B1:B10,C1:C10)

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

I find myself asking a lot of questions on these boards lately... Thank
you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT()
would
do - the catch is that I only want to multiply pairs where the
corresponding
cell in Column A contains the text "YELLOW". For instance, in this
case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

... I would want to return [(2*6) + (4*12)] = 60.

 

[PaladinWhite]

Hey, that works too! Thanks, Tyro.

=SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5)

Tyro

I find myself asking a lot of questions on these boards lately... Thank you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT() would
do - the catch is that I only want to multiply pairs where the
corresponding
cell in Column A contains the text "YELLOW". For instance, in this case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

... I would want to return [(2*6) + (4*12)] = 60.

 

[Tyro]

Simple matter of coersion, "--" or "*" in this case.

Tyro

Hey, that works too! Thanks, Tyro.

=SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5)

Tyro

I find myself asking a lot of questions on these boards lately... Thank
you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT()
would
do - the catch is that I only want to multiply pairs where the
corresponding
cell in Column A contains the text "YELLOW". For instance, in this
case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

... I would want to return [(2*6) + (4*12)] = 60.

 

[PaladinWhite]

You've both explained it very well... I see that the -- is simply allowing
the value of the next statement to act as an "eliminator" of sorts so I only
get multiplication through on the rows that I wanted.

I can already think of other places where I can implement that trick. Thanks
again.

Simple matter of coersion, "--" or "*" in this case.

Tyro

Hey, that works too! Thanks, Tyro.

=SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5)

Tyro

I find myself asking a lot of questions on these boards lately... Thank
you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT()
would
do - the catch is that I only want to multiply pairs where the
corresponding
cell in Column A contains the text "YELLOW". For instance, in this
case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

... I would want to return [(2*6) + (4*12)] = 60.

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

You've both explained it very well... I see that the -- is simply allowing
the value of the next statement to act as an "eliminator" of sorts so I
only
get multiplication through on the rows that I wanted.

I can already think of other places where I can implement that trick.
Thanks
again.

Simple matter of coersion, "--" or "*" in this case.

Tyro

Hey, that works too! Thanks, Tyro.

:

=SUMPRODUCT((A1:A5="yellow")*B1:B5*C1:C5)

Tyro

message
I find myself asking a lot of questions on these boards lately...
Thank
you
to all you great people that make the time to help!

I'm trying to multiply values in Columns B and C , like SUMPRODUCT()
would
do - the catch is that I only want to multiply pairs where the
corresponding
cell in Column A contains the text "YELLOW". For instance, in this
case:

PURPLE | 1 | 3
YELLOW | 2 | 6
PURPLE | 3 | 9
YELLOW | 4 | 12
PURPLE | 5 | 15

... I would want to return [(2*6) + (4*12)] = 60.