Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using SUM and getting #Value!
The sheets are not next to eachother although in the same document
-- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|