Summing cells in a range that has #VALUE!

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

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
>

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" <csvision@discussions.microsoft.com> wrote in message
news:7C6A14BC-D8B2-4520-8A78-08F5F2338EE6@microsoft.com...
>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

"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" <csvision@discussions.microsoft.com> wrote in message
> news:7C6A14BC-D8B2-4520-8A78-08F5F2338EE6@microsoft.com...
> >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.
>
>