subtotals function error
 

I have a spreadsheet which uses the subtotal function in several rows to give
me sutotals for groups within groups which can then be filtered by column
headings. I have then grouped and outlined the spreadsheet .

This all worked perfectly but suddenly the subtotals are only working when
all the groups are expanded, when closed the subtotals show as zero. I have
a back up copy from the previous week which is still working fine and the
only difference I can see between the two copies is the fact that most of the
row numbers in the faulty copy are shown in blue then revert to black whereas
the back up copy row numbers are all shown in black.

I have tried ungrouping and starting again but it doesn't make any
difference. Does anyone have any clues what's happened and what is the
significance of the blue row numbers??

Thanks

subtotals function error
 

The blue row numbers indicate that you have applied a filter - these
are rows within the filter range which match the filter condition.

When you use SUBTOTAL(9,range), for example, the subtotal only adds
the values in the displayed rows, so if some have been filtered out
then the subtotal will have a different value.

All this is standard Excel stuff - not an error.

Hope this helps.

Pete

On May 14, 11:37 am, Mar_W wrote:
I have a spreadsheet which uses the subtotal function in several rows to give
me sutotals for groups within groups which can then be filtered by column
headings. I have then grouped and outlined the spreadsheet .

This all worked perfectly but suddenly the subtotals are only working when
all the groups are expanded, when closed the subtotals show as zero. I have
a back up copy from the previous week which is still working fine and the
only difference I can see between the two copies is the fact that most of the
row numbers in the faulty copy are shown in blue then revert to black whereas
the back up copy row numbers are all shown in black.

I have tried ungrouping and starting again but it doesn't make any
difference. Does anyone have any clues what's happened and what is the
significance of the blue row numbers??

Thanks

subtotals function error
 

Thanks, Pete, I had already checked that there were no filters on and
couldn't locate any. However, I have now taken the autofilters off,
reapplied them and hey presto it's now working fine!!!

"Pete_UK" wrote:

The blue row numbers indicate that you have applied a filter - these
are rows within the filter range which match the filter condition.

When you use SUBTOTAL(9,range), for example, the subtotal only adds
the values in the displayed rows, so if some have been filtered out
then the subtotal will have a different value.

All this is standard Excel stuff - not an error.

Hope this helps.

Pete

On May 14, 11:37 am, Mar_W wrote:
I have a spreadsheet which uses the subtotal function in several rows to give
me sutotals for groups within groups which can then be filtered by column
headings. I have then grouped and outlined the spreadsheet .

This all worked perfectly but suddenly the subtotals are only working when
all the groups are expanded, when closed the subtotals show as zero. I have
a back up copy from the previous week which is still working fine and the
only difference I can see between the two copies is the fact that most of the
row numbers in the faulty copy are shown in blue then revert to black whereas
the back up copy row numbers are all shown in black.

I have tried ungrouping and starting again but it doesn't make any
difference. Does anyone have any clues what's happened and what is the
significance of the blue row numbers??

Thanks