I have a range of 8000 cells that contain #VALUE!, I need to find the sum
for the cells contained in the range with numbers.

=sum(if(isnumber(a1:b4000),a1:b4000))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

cs_vision wrote:

I have a range of 8000 cells that contain #VALUE!, I need to find the sum
for the cells contained in the range with numbers.

--

Dave Peterson

Try this:

=SUMIF(A1:A8000,"<#value!")

Of course, the correct thing to do is to correct the errors in the column.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"cs_vision" wrote in message
...
I have a range of 8000 cells that contain #VALUE!, I need to find the sum
for the cells contained in the range with numbers.

cs_vision Wrote:
I have a range of 8000 cells that contain #VALUE!, I need to find the
sum
for the cells contained in the range with numbers.

Try changing the formula that creates the #value! error so it doesn't.
ie
=if(a1=0,"",a2/a1)

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=562244

Thank you

"Ragdyer" wrote:

Try this:

=SUMIF(A1:A8000,"<#value!")

Of course, the correct thing to do is to correct the errors in the column.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"cs_vision" wrote in message
...
I have a range of 8000 cells that contain #VALUE!, I need to find the sum
for the cells contained in the range with numbers.

Thank You

"Dave Peterson" wrote:

=sum(if(isnumber(a1:b4000),a1:b4000))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

cs_vision wrote:

I have a range of 8000 cells that contain #VALUE!, I need to find the sum
for the cells contained in the range with numbers.

--

Dave Peterson