SumProduct problem

[John]

How do I fix the following to sum the column & not get a #NAME? error?
=SUMPRODUCT(--(IsNumeric($D$10:$D$500)), --($D$10:$D$500))

I appreciate your help, -John

 

[Pecoflyer]

How do I fix the following to sum the column & not get a #NAME? error?
=SUMPRODUCT(--(IsNumeric($D$10:$D$500)), --($D$10:$D$500))

I appreciate your help, -John

Hi,
I don't know ISNUMERIC. Maybe you need ISNUMBER?


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faster

 

[xlmate]

remove Isnumeric, Excel does not recognize this
I don't understand your formula, if you are adding up all in col D10 to D500
you can simply use the Sum function.
--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis

 

[Lars-Åke Aspelin]

Hi,
I don't know ISNUMERIC. Maybe you need ISNUMBER?

But if
=SUMPRODUCT(--(ISNUMBER($D$10:$D$500)),--($D$10:$D$500))

gives the wanted result, you can simplify that to

=SUM($D$10:$D$500)

Hope this helps / Lars-Åke

 

[Rick Rothstein]

IsNumeric is the VB function for testing if a value is a number or not...
ISNUMBER is the worksheet function equivalent of that. Also, you don't
really need some of those parentheses as what they surround are
self-contained. In addition, you do not want to use the double unary (minus
signs) in front of the cell reference (if the cell contains text, it will
produce an error... plus, that is why you are using the ISNUMBER function).
This is the formula you should have tried...

=SUMPRODUCT(--ISNUMBER($D$10:$D$500),$D$10:$D$500)

HOWEVER, you don't need to have your formula do all that work... SUM will
skip over text and only add numbers. So, you can use this formula instead
and it should do what you want...

=SUM($D$10:$D$500)

 

[John]

Bingo, Thx so much Rick. Removing the -- did the trick and your right about
the IsNumeric. The reason I didn't use SUM was because there are several
other cols I have in the real SUMPRODUCT and they all were working so I
didn't want to confuse the question.

Thx again, John