Formula Omits Adjacent Cells

[Excel Hater]

I have filters on and I'm using the SUM to add a group of numbers. It seems
that excel automatically adds adjacent cells that I have hidden(I'm guessing
it's adding the hidden cells cause the result is way off and I get a formula
error however; the formula does not reflect these additional cells). I have
unselected the extend data range option, but this only works the first time.
After I start working on the spreadsheet and add more SUM formulas it reverts
back to adding the hidden cells.

I have thousand of numbers so it's not efficient to select each one
individually and I don't remember having to do that before. I used to be
able to use the shift key while selecting large data ranges without it
selecting hidden cells in between.

Is there a way I can permanently disable Excel from automatically including
adjacent cells when using SUM?

 

[Peo Sjoblom]

Adjacent cells has nothing to do with it per se, if you sum a range hidden
cells
will be included if they are a part of that range

if you have Excel 2003 and later you can use

=SUBTOTAL(109,range)

and it will only sum visible cells, for earlier version you need VBA


--


Regards,


Peo Sjoblom

 

[George Nicholson]

Take a look at SUBTOTAL(). You can tell it to exclude Hidden/filtered cells
or not.

 

[Gary''s Student]

To analyze filtered data use the SUBTOTAL function.

 

[Excel Hater]

This worked.

Gracias.

Adjacent cells has nothing to do with it per se, if you sum a range hidden
cells
will be included if they are a part of that range

if you have Excel 2003 and later you can use

=SUBTOTAL(109,range)

and it will only sum visible cells, for earlier version you need VBA


--


Regards,


Peo Sjoblom

 

[MartinW]

Hi Peo,

Just FYI, Excel 2000 has the SUBTOTAL function
although the syntax is slightly different.

Excel 2003 =SUBTOTAL(109,range)
Excel 2000 =SUBTOTAL(9,range)

Regards
Martin

 

[Peo Sjoblom]

Hi Martin,

true but it won't work for hidden rows, just filtered.
It was added in 2003 and they just added 100 to the previous numbers, you
can still use
9 in 2003 if you only want to sum filtered rows


--


Regards,


Peo Sjoblom

 

[MartinW]

Thanks Peo, My apologies!

Regards
Martin

Hi Martin,

true but it won't work for hidden rows, just filtered.
It was added in 2003 and they just added 100 to the previous numbers, you
can still use
9 in 2003 if you only want to sum filtered rows


--


Regards,


Peo Sjoblom