Can you subtotal data in rows?
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))}
|