"SUMPRODUCTIF"
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)
"PaladinWhite" wrote in message
...
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!
"Ron Coderre" wrote:
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)
"PaladinWhite" wrote in 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.
|