View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
MartinW MartinW is offline
external usenet poster
 
Posts: 860
Default Formula Omits Adjacent Cells

Thanks Peo, My apologies!

Regards
Martin


"Peo Sjoblom" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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


"Excel Hater" <Excel wrote in message
...
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?