Spiky,
No the Subtotal function has not changed in Excel 2007, it still
doesn't handle horizontal ranges (for the life of me I can't figure
out why they don't have this option).

Thanks for the suggestions, I don't have a problem with the manual
calc since the spreadsheet is set up that way anyway. I will look for
the external UDF and see if that works - otherwise, and unless someone
comes up with a better solution, I may just find myself having to
create an additional table where I transpose the data to a vertical
orientation and use Subtotal and then bring the value back to the
horizontal table. Rather a long way around what should be a fairly
easy problem to solve.

Your other suggestion using CELL is no-go, too many columns (over 50).

Again, thanks.

hdf wrote:
Spiky,
No the Subtotal function has not changed in Excel 2007, it still
doesn't handle horizontal ranges (for the life of me I can't figure
out why they don't have this option).

Thanks for the suggestions, I don't have a problem with the manual
calc since the spreadsheet is set up that way anyway. I will look for
the external UDF and see if that works - otherwise, and unless someone
comes up with a better solution, I may just find myself having to
create an additional table where I transpose the data to a vertical
orientation and use Subtotal and then bring the value back to the
horizontal table. Rather a long way around what should be a fairly
easy problem to solve.

Your other suggestion using CELL is no-go, too many columns (over 50).

Again, thanks.

Use a "helper row". Put

=CELL("width")

in each cell in an unused row on this sheet

- or -

put

=CELL("width",Your_Sheet!A1)

in A1 on an unused sheet in the same workbook and copy across as needed.


Then you can use a Sum/If array formula like the following (assuming your data
is in B2:I2):

{=SUM(IF(B1:I10,B2:I2,0))}

- or -

{=SUM(IF(Helper_Sheet!B1:I10,B2:I2,0))}