I know the subtotal function will not work with horizontal ranges. My
question is what can I use to substitute it?

I have a horizontal output data table, each column represents a year
of data, and the number of years shown can change based on the users
needs (it's a cashflow statement).

What function or formula can I use in a cell at the beginning of a
given row that will add only the data visible in that given row -
disregarding the data in the hidden columns?

Thanks for the help?

Sorry, forgot to mention that I'm using Excel 2007, in case that makes
a difference.

If you look up the SUBTOTAL() function in Help you'll find that you can
perform a number of calculations - not just 'sums' and depending on the
parameter value you supply, Excel will disregard hidden cells.

So...if you truly want a subtotal, use

=SUBTOTAL(109,range of cells)

the 109 directs that hidden cells be ignored when summing

"hdf" wrote:

> Sorry, forgot to mention that I'm using Excel 2007, in case that makes
> a difference.
>

On Aug 25, 2:14 pm, Duke Carey <DukeCa...@discussions.microsoft.com>
wrote:
> If you look up the SUBTOTAL() function in Help you'll find that you can
> perform a number of calculations - not just 'sums' and depending on the
> parameter value you supply, Excel will disregard hidden cells.

Still doesn't work horizontally, though. Unless that changed in Excel
2007.

This works. Couple of issues with it. It includes a UDF from morefunc,
available online for free. And it doesn't calculate automatically for
hiding/unhiding columns, you would have to press F9 to check. It does
calculate automatically if you actually change a number in a cell.

=SUMPRODUCT(B1:E1,--(XLM.GET.CELL(53,B1:E1)<>""))

This also works. The CELL function should also be able to do this, but
it can only handle one cell at a time, no arrays. So you have to add
each cell individually, killing the neat SUM function that has existed
for some time. That might be a serious pain after a while. But it
doesn't need a download of 3rd party software, or you to write your
own UDF. Note that this also would not auto-calculate, must press F9.
=SUM(IF(CELL("width",B1)=0,0,B1),IF(CELL("width",C1)=0,0,C1),IF(CELL("width",D1)=0,0,D1),IF(CELL("width",E1)=0,0,E1))

Duke Carey wrote:
> If you look up the SUBTOTAL() function in Help you'll find that ...


The SUBTOTAL function is designed for columns of data, or vertical ranges. It is
not designed for rows of data, or horizontal ranges. For example, when you
subtotal a horizontal range using a function_num of 101 or greater, such as
SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a
row in a subtotal of a vertical range does affect the subtotal.

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.

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:I1>0,B2:I2,0))}

- or -

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

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.