Sum?
 

I have a column H with 121 rows, in some rows there are
numbers in others there are #DIV/0!
I need to know the total of the numbers in column H
eg:- H
$10
$10
$12
$18
#DIV/0!
#DIV/O!
#DIV/0!
The column can be up to 121 rows with numbers or 60
or so rows with numbers and the rest with #DIV/0!

Thanks Heaps Bill Mackay

Hi Bill,

Try this

=SUM(IF(NOT(ISERROR(A1:A121)),A1:A121))

which is an array formula, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bill mackay" wrote in message
...
I have a column H with 121 rows, in some rows there are
numbers in others there are #DIV/0!
I need to know the total of the numbers in column H
eg:- H
$10
$10
$12
$18
#DIV/0!
#DIV/O!
#DIV/0!
The column can be up to 121 rows with numbers or 60
or so rows with numbers and the rest with #DIV/0!

Thanks Heaps Bill Mackay

Bobs answer will give you what you need, but personally I couldn't sleep
till I had fixed the formulas that were returning #DIV/0 in the first place.
I would always fix errors at source rather than compensating for their
effects.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"bill mackay" wrote in message
...
I have a column H with 121 rows, in some rows there are
numbers in others there are #DIV/0!
I need to know the total of the numbers in column H
eg:- H
$10
$10
$12
$18
#DIV/0!
#DIV/O!
#DIV/0!
The column can be up to 121 rows with numbers or 60
or so rows with numbers and the rest with #DIV/0!

Thanks Heaps Bill Mackay

Take notice of Ken's observation. Otherwise...

=SUMIF(A1:A121,"<#DIV/0!")

which just needs enter.

bill mackay wrote:
I have a column H with 121 rows, in some rows there are
numbers in others there are #DIV/0!
I need to know the total of the numbers in column H
eg:- H
$10
$10
$12
$18
#DIV/0!
#DIV/O!
#DIV/0!
The column can be up to 121 rows with numbers or 60
or so rows with numbers and the rest with #DIV/0!

Thanks Heaps Bill Mackay