Using SUM and getting #Value!
 

=SUM('Debt #1 Old'!L30,'Debt #2 Old'!L30,'Debt #3 Old'!L30,'Debt #
Old'!L30,'Debt #5 Old'!L30,'Debt #6 Old'!L30,'Debt #7 Old'!L30,'Debt #
Old'!L30,'Debt #9 Old'!L30,'Debt #10 Old'!L30,'Debt #11 Old'!L30,'Deb
#12 Old'!L30,'Debt #13 Old'!L30,'Debt #14 Old'!L30,'Debt #1
Old'!L30,'Debt #16 Old'!L30,'Debt #17 Old'!L30,'Debt #18 Old'!L30,'Deb
#19 Old'!L30)


The above is all of the cells I need added together. Some of them hav
nothing in them so I get #VALUE! back for the aboves answer. What woul
be an easy way to tell it to ignore anything that isn't a number grate
than zero?

Thanks.
Ronni

--
Message posted from http://www.ExcelForum.com

Using SUM and getting #Value!
 

Would
=SUM('Debt #1 Old:Debt #19 Old'!L30)
work?
Or perhaps the sheets are not adjacent?

--
Return email address is not as DEEP as it appears
"rsheppick " wrote in message
...
=SUM('Debt #1 Old'!L30,'Debt #2 Old'!L30,'Debt #3 Old'!L30,'Debt #4
Old'!L30,'Debt #5 Old'!L30,'Debt #6 Old'!L30,'Debt #7 Old'!L30,'Debt #8
Old'!L30,'Debt #9 Old'!L30,'Debt #10 Old'!L30,'Debt #11 Old'!L30,'Debt
#12 Old'!L30,'Debt #13 Old'!L30,'Debt #14 Old'!L30,'Debt #15
Old'!L30,'Debt #16 Old'!L30,'Debt #17 Old'!L30,'Debt #18 Old'!L30,'Debt
#19 Old'!L30)


The above is all of the cells I need added together. Some of them have
nothing in them so I get #VALUE! back for the aboves answer. What would
be an easy way to tell it to ignore anything that isn't a number grater
than zero?

Thanks.
Ronnie


---
Message posted from http://www.ExcelForum.com/

Using SUM and getting #Value!
 

The sheets are not next to eachother although in the same document

--
Message posted from http://www.ExcelForum.com

Using SUM and getting #Value!
 

Ronnie,
Are you sure your #VALUE result is from no entries in some cells rather than
an error value somewhere in the range ?

NickHK

"rsheppick " wrote in message
...
The sheets are not next to eachother although in the same document.


---
Message posted from http://www.ExcelForum.com/

Using SUM and getting #Value!
 

Yes,

That is the problem the #Value is because some of the sheets that
wish to add have that as the restult in the square I am trying to =sum

I am trying to find a formula that will allow me to use the previou
noted forumla but say if it =error or=<=0.00 then ignore it an
contiune on until the end.

Understand what I am saying lol :confused

--
Message posted from http://www.ExcelForum.com

Using SUM and getting #Value!
 

Nice challenge.
I couldn't have got there without the help of Frank Kabel and Aladin
Akyurek. I still haven't managed to get a solution that uses SUMPRODUCT()
in a non-array formula (it works until I start testing for error values).
And don't ask me why you need the N() function, but this seems to work:

Array entered (with Control+Shift+Enter):

=SUM(IF(NOT(ISERROR(N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30")))),N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30"))))

--
Return email address is not as DEEP as it appears
"rsheppick " wrote in message
...
Yes,

That is the problem the #Value is because some of the sheets that I
wish to add have that as the restult in the square I am trying to =sum

I am trying to find a formula that will allow me to use the previous
noted forumla but say if it =error or=<=0.00 then ignore it and
contiune on until the end.

Understand what I am saying lol :confused:


---
Message posted from http://www.ExcelForum.com/

Using SUM and getting #Value!
 

Further to my last post, I hadn't noticed that you also wanted it to sum
only positive values. Easily adapted:

Array entered:

=SUM(IF(NOT(ISERROR(N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30")))),N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30"))*(N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30"))0)))

--
Return email address is not as DEEP as it appears
"rsheppick " wrote in message
...
Yes,

That is the problem the #Value is because some of the sheets that I
wish to add have that as the restult in the square I am trying to =sum

I am trying to find a formula that will allow me to use the previous
noted forumla but say if it =error or=<=0.00 then ignore it and
contiune on until the end.

Understand what I am saying lol :confused:


---
Message posted from http://www.ExcelForum.com/

Using SUM and getting #Value!
 

Incidentally I tried (without success) the following rather shorter solution
to test for positive values. Any responses to why it fails would extend my
understanding one more iota and would be appreciated:

{=SUM(IF(NOT(ISERROR(N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30")))),MAX(0,N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30")))))}

--
Return email address is not as DEEP as it appears
"Jack Schitt" wrote in message
...
Further to my last post, I hadn't noticed that you also wanted it to sum
only positive values. Easily adapted:

Array entered:

=SUM(IF(NOT(ISERROR(N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30")))),N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30"))*(N(INDIRECT("'Debt #" & ROW(INDIRECT("$1:$19")) & "
Old'!L30"))0)))

--
Return email address is not as DEEP as it appears
"rsheppick " wrote in message
...
Yes,

That is the problem the #Value is because some of the sheets that I
wish to add have that as the restult in the square I am trying to =sum

I am trying to find a formula that will allow me to use the previous
noted forumla but say if it =error or=<=0.00 then ignore it and
contiune on until the end.

Understand what I am saying lol :confused:


---
Message posted from http://www.ExcelForum.com/