Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM() of empty dynamic named range returns #N/A
I have a cell with =SUM(CamperRev) where CamperRev is a dynamically
named range containing contribution amounts. This works fine if there are any contributions, but displays #N/A if there are not. I worked around this with =IF(ISNUMBER(SUM(CamperRev)),SUM(CamperRev),"") but was wondering if there is a better way. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM() of empty dynamic named range returns #N/A
=sum() ignores text and empty cells.
So if you got an error from the =sum() function, that means that you have an error in that range. You could use an array formula: =sum(if(isnumber(camperrev),camperrev)) 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.) And camperrev can't refer to a whole column except in xl2007. Or you could change the formula that is used in the cells in camperrev. Instead of returning an error, you could return a 0 or an empty string (""). Aviashn wrote: I have a cell with =SUM(CamperRev) where CamperRev is a dynamically named range containing contribution amounts. This works fine if there are any contributions, but displays #N/A if there are not. I worked around this with =IF(ISNUMBER(SUM(CamperRev)),SUM(CamperRev),"") but was wondering if there is a better way. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SUM() of empty dynamic named range returns #N/A
Thanks for your response. The array formula is a superior solution, I hadn't considerd using an array at all. Your post (and solution) have still left me confused, however, about the exact nature of the problem. "=sum() ignores text and empty cells. So if you got an error from the =sum() function, that means that you have an error in that range. " AND "Or you could change the formula that is used in the cells in camperrev." All the cells are empty. No formulas or values. If a value is entered it works fine. "And camperrev can't refer to a whole column except in xl2007. " camperrev refers to a dynamically named range using the following formula: =OFFSET(Contributions!$H$2,0,0,MATCH(1E+306,Contri butions!$H:$H,1),1) As a result, the range is only as large as the amount of entries. Perhaps the problem is that in this case the range object returns Nothing? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass named range to user defined function returns #value! | Excel Programming | |||
Pass named range to user defined function returns #value! | Excel Programming | |||
HLOOKUP using a named range returns #VALUE! | Excel Worksheet Functions | |||
Trapping error from Empty Dynamic Named Range | Excel Programming | |||
Named range returns 1004 error | Excel Programming |