Function_num

what are you actually trying to do?

You can choose a function No. from 101 to 111 for the function to ignore hidden rows. i.e 109 for SUM, 101 AVERAGE etc. I think this feature is available from Excell 2003.

This function ignores any nested subtotals. Also, when used with a data filter it only uses the displayed values.

Cheers

Thanks, but I have tried what you suggest.
The 109 function does not include or exclude columns if they are hidden. The function only works on rows.

I am looking for a function that excludes columns if they are hidden or grouped and includes them if they are expanded.

Can anyone help me with this function?:

its part of the subtotal syntax for including or excluding hidden rows:
=SUBTOTAL(Function_num,[ref1],[ref2]) (9)

Is there a way to get this function to exclude hidden columnn as well.
currently it only works to exclude hidden (grouped) rows.

Thanks.

Click to expand...

See the 4th Remark in the SUBTOTAL help topic.

Thanks, but i know that.
I am asking if there is a similar function that does affect columns.

Thanks, but i know that.
I am asking if there is a similar function that does affect columns.

Click to expand...

No.

Thanks

However, absent a native function for the job, you can construct a formula that will operate only on visible cells.

what are you actually trying to do? what data have you got? what results do you expect?

I am not sure how to explain.

Assume I have 3 colums per month for 3 months.
Sales, Returns and Commision.

Going down the rows I have different product codes and the data therfore shows for example what the sales value of product code 110456 in each month is. The same is obviously true of returns and consequently commision.

ITO formatting I group each month: Data > Group and Outline > Group.

What I want is to total all the sales for each month, all the returns for a month and all the commision.
However if the grouping of a particular month is closed - hidden then the calculation must ignore those figures, but must include them if the groping is open, i.e the months figures can be seen.

Type 10 into A1 and 20 into A2. In B3 type =subtotal(109,A1,A2)

Then select row two, go to Data, Group and Outline click Group. There will be a little minus sign on the left of the row 2. As you click it, you will notice the result of the subtotal change from 30 to 10 as you either group or ungroup row 2. I want to do exactly this but I group columns, not rows and there seems to be no function that allowes me to do that.

What do I do?

Thanks.

I hope this is clear.
[/quote]