#VALUE! and SUBTOTAL

[SamuelT]

Hi all,

I've got a column with a bunch of figures in them. Another column is
often autofiltered and I've found that SUBTOTAL seems to be a good
function to sum the figures when the autofilter is on.

However, and it seems to have happened all of a sudden (I'm sure it
used to work!), my cell is now showing a #VALUE! error message due to a
number of the same error message in a number of the cells in the column.
I've tried to experiment with ISNUMBER and ISERROR formulas to overcome
this, but no joy as of yet. Can anyone suggest a formula that will
work. FYI - my subtotal formula is:

=SUBTOTAL(9,L3:L351)

TIA,

SamuelT

 

[Peo Sjoblom]

You can use a help column and isnumber, =ISNUMBER(L3) and copy down

then you filter on that help column on TRUE, then filter on the column with
the numbers

However since value errors derives from calculation with text, why don't you
fix them so that L3:L351 does not contain any errors?

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey