Change in function_num in Excel 2003

[Guest]

I have upgraded to Excel 2003, but many of the people I share workbooks with have not. I have noticed that in Excel 2003 there is a new level of function_nums under subtotal (101-111) which are for subtotalling only hidden rows. In older versions this does not exist so anytime I send a worksheet with a subtotal of this type the formulas are all zeros, as there is no function_num 109 in that version of Excel. As an example = subtotal(109,a1:a1000) will only include visible rows in the subtotal in Excel 2003 but in earlier versions it returns zero.

I thought in earlier versions =subtotal(9,a1:a1000) would only include visible rows, not all rows, but I cannot go back and check as I do not have an older version. Is there a subtotal that will only include visible rows that is the same in Excel 2003 and earlier versions?

 

[Frank Kabel]

Hi
the difference between Excel 2003 and older version is the following:
SUBTOTAL([1-9],range)
works the same in all version: It processes only the visible rows wich
are shown by a FILTERED list.

The new synatx
SUBTOTAL([101-109],range)
only works in Excel 2003 (returns zero in older versions). It processes
also only visible rows, but in this case the hidden rows are created
MANUALLY and not by a filter.

 

[Guest]

Is there any way in older versions to subtotal only visible rows created manually, not through a filter? The worksheet I am using does not use filters, rather it uses VBA code to only reveal certain rows of data.

Hi
the difference between Excel 2003 and older version is the following:
SUBTOTAL([1-9],range)
works the same in all version: It processes only the visible rows wich
are shown by a FILTERED list.

The new synatx
SUBTOTAL([101-109],range)
only works in Excel 2003 (returns zero in older versions). It processes
also only visible rows, but in this case the hidden rows are created
MANUALLY and not by a filter.


--
Regards
Frank Kabel
Frankfurt, Germany

I have upgraded to Excel 2003, but many of the people I share
workbooks with have not. I have noticed that in Excel 2003 there is
a new level of function_nums under subtotal (101-111) which are for
subtotalling only hidden rows. In older versions this does not exist
so anytime I send a worksheet with a subtotal of this type the
formulas are all zeros, as there is no function_num 109 in that
version of Excel. As an example = subtotal(109,a1:a1000) will only
include visible rows in the subtotal in Excel 2003 but in earlier
versions it returns zero.

I thought in earlier versions =subtotal(9,a1:a1000) would only
include visible rows, not all rows, but I cannot go back and check as
I do not have an older version. Is there a subtotal that will only
include visible rows that is the same in Excel 2003 and earlier
versions?

 

[Frank Kabel]

Hi
in older versions you'll need VBA (a user defined function) for this

--
Regards
Frank Kabel
Frankfurt, Germany

Is there any way in older versions to subtotal only visible rows
created manually, not through a filter? The worksheet I am using
does not use filters, rather it uses VBA code to only reveal certain
rows of data.

Hi
the difference between Excel 2003 and older version is the following:
SUBTOTAL([1-9],range)
works the same in all version: It processes only the visible rows
wich are shown by a FILTERED list.

The new synatx
SUBTOTAL([101-109],range)
only works in Excel 2003 (returns zero in older versions). It
processes also only visible rows, but in this case the hidden rows
are created MANUALLY and not by a filter.


--
Regards
Frank Kabel
Frankfurt, Germany

I have upgraded to Excel 2003, but many of the people I share
workbooks with have not. I have noticed that in Excel 2003 there is
a new level of function_nums under subtotal (101-111) which are for
subtotalling only hidden rows. In older versions this does not
exist so anytime I send a worksheet with a subtotal of this type the
formulas are all zeros, as there is no function_num 109 in that
version of Excel. As an example = subtotal(109,a1:a1000) will only
include visible rows in the subtotal in Excel 2003 but in earlier
versions it returns zero.

I thought in earlier versions =subtotal(9,a1:a1000) would only
include visible rows, not all rows, but I cannot go back and check
as I do not have an older version. Is there a subtotal that will
only include visible rows that is the same in Excel 2003 and earlier
versions?