Sumif with multiple columns in sum_range
The Double-Unary (--) forces a conversion of TRUE/FALSE values to numbers.
--TRUE = 1
--FALSE = 0
so...--{TRUE, TRUE, FALSE}...becomes {1,1,0}
You could also multiply boolean values to the same effect:
1*TRUE = 1
1*FALSE = 0
But, the Dbl-Minus indicates to knowledgable users that
a numeric conversion is intended, versus a calculation.
Regarding your #VALUE! error....scan the referenced data and see if
there are any errors or irregularities in it.
Does that help?
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"AFJr" wrote in message
...
Hi Ron,
Thanks for your quick response.
I'm getting a #VALUE! error.
Sorry for not posting that earlier. I'm getting this error using both
structures of the formula. I'm stumped, any ideas?
One more question, the "--" =SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62)
what does that mean? I've tried it with and w/o that, still no luck.
--
TIA
AFJr
"Ron Coderre" wrote:
Hi, AF Jr
If you're not getting a #NAME! error,
then Excel 97 has the SUMPRODUCT
function.
There are 2 variations of the structure.
If this one doesn't work:
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
Try this one:
=SUMPRODUCT(--($B$21:$B$62="C"),$Q$21:$S$62)
Does that help?
Post back if you have more questions.
Oh, and thanks for the feedback on the explanation.
Much appreciated.
--------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"AFJr" wrote in message
...
Hi Ron,
I wanted to thank you for your GREAT explanation:
**********************************
We'll exploit that feature in SUMPRODUCT.....
In this formula:
=SUMPRODUCT((A2:A10="Joel")*B2:C10)
This expression:
(A2:A10="Joel")....returns a series of TRUE/FALSE values
depending on whether the cell equals "Joel" or not.
and
B2:C10 contains NUMBERS!
SO..
Each TRUE when multiplied by its associated NUMBER
returns that number.
Each FALSE when multiplied by its associated NUMBER
returns converts to a zero...0 x number = 0.
SUMPRODUCT returns the sum of all those results
which is the sum of all combinations where
Col_A="Joel"
My question is this, I'm using Excel97, does this function work in this
version?
My formula
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
is not working. I thought I understood exactly what you were saying,
maybe
not.....
--
TIA
AFJr
|