Using SUM and getting #Value!

[rsheppick]

=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

 

[Jack Schitt]

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

 

[rsheppick]

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

 

[NickHK]

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]

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

 

[Jack Schitt]

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"))))

 

[Jack Schitt]

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)))

 

[Jack Schitt]

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

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)))