Ron
Thu Mar 27 08:17:16 CDT 2008
It's time to try another approach:
Try this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER):
=SUM(IF($B$21:$B$62="C",IF(ISNUMBER($Q$21:$S$62),$Q$21:$S$62)))
Does that help?
-------------------------
Regards,
Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
"AFJr" <AFJr@discussions.microsoft.com> wrote in message
news:C0067CB5-DC0F-443A-B4D4-5FBAFD0733A2@microsoft.com...
> 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" <AFJr@discussions.microsoft.com> wrote in message
>> news:80BD6330-4621-42B7-A101-C8C3ADABFAF1@microsoft.com...
>> > 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" <AFJr@discussions.microsoft.com> wrote in message
>> >> news:774723DC-7F3B-4F6C-8AA6-AE7E13707337@microsoft.com...
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>> >>
>>
>>
>>