Sumif with multiple columns in sum_range
Hi Ron,
Thanks for sticking with me through this. I've checked the data for errors.
I'm not sure if this is a problem or not.
The targeted cells in my formula:
=SUMPRODUCT(($B$21:$B$62="C")*$Q$21:$S$62)
B21:B62 are just text selected from a list.
Q21:S62 contain formulas, columns Q & R formula is: =IF($E:$E=0,"",$E:$E*0)
{the zero that is multiplied could be a value or zero}
column S is: =IF($E:$E=0,"",(Q22+R22)*0.15)
(the data in column "E" is a number that represents a "Qty" multiplier)
So when the SUMPRODUCT function is executed it looks at column B to see if
the value ="C" and produces a "1" if it is(True). If it evaluates to "1",
columns Q:S formula's are executed and added together. Being that a zero
could result in the the formula's in columns Q:S I suspect this is giving me
my #VALUE! error. Multiplying any number by zero results in zero but, why
would this be a problem? Maybe its the way excel evaluates it. If so, I've
got to come up with another way to do this.
Does this make sense to you? Any other suggestions?
--
TIA
AFJr
"Ron Coderre" wrote:
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
|