Formula Omits Adjacent Cells

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?

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?

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

--
HTH,
George


"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?

To analyze filtered data use the SUBTOTAL function.
--
Gary''s Student - gsnu200753


"Excel Hater" wrote:

> 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?

This worked.

Gracias.

"Peo Sjoblom" wrote:

> 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?
>
>
>

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?
>
>

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?
>>
>>
>
>

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?
>>>
>>>
>>
>>
>
>