It works great accept when I refilter data in the table. Then it says #VALUE!
and I have to reset it for each cell I have the formula entered into. Is
there a way around this?
--
Thank you!


"Chip Pearson" wrote:


Your formula appears to be correct -- I don't see anything wrong with
it. The first thing that comes to mind is that you have an #N/A error
in your input data in CF3:CF1000. This error will cause an #N/A in the
formula. Use the following revised array formula to exclude #N/A
errors from the calculation:

=SUM(IF(ISNA(A1:A6),0,IsVisible(A1:A6)*(A1:A61000 )*ISNUMBER(A1:A6)))

If this is not the problem, then you need to debug the formula and the
VBA code. The first way to test to find the cause of the #N/A is to
use the Formula Evaluate tool on the Auditing command bar to evaluate
the formula one step at a time. If the #N/A is coming out of the VBA
function, go to VBA, select the ReDim statement and press F9 to set a
breakpoint on that line. Then, back in Excel, press F2 and then CTRL
SHIFT ENTER to force the formula to recalculate. Code execution will
pause on the breakpoint, and you can then use F8 to step through the
code line by line. If the code just terminates on a line other than
at the normal exit point at the end of the procedure, that line is
causing a problem.

I've tried various combinations of data and visible/hidden cells and
cannot get the function to return #N/A unless there is an #N/A in the
input data.

I can probably use it on other applications.

That's why I write functions to be general and generic -- once
written, they can be used in any number of situations. After a while,
you accumulate a large library of very useful functions. This
particular functions is described on my web site at
http://www.cpearson.com/Excel/IsVisible.aspx.


Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]







On Mon, 21 Dec 2009 21:44:01 -0800, Doug
wrote:

I am excited about the functionality of this. I can probably use it on other
applications. Could you please check and make sure this is right. I entered
it as you said and the curly braces appeared, but it says #N/A in the cell.
I placed the function in a separate module where I assume it should go?

=SUM(IsVisible(CF3:CF1000)*(CF3:CF1000<2000)*ISNU MBER(CF3:CF1000))
.