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" wrote in message
...
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