Solved: formula to collection

[thair younis]

Hi,
I want formula to collect numbers on column , like (sum) formula, but when i
hide any row from column subtract from formula collect
For Example
cell A1 = 10
cell A2 = 10
cell A3= 10
cell A4= 10
Cell A5=10

If i use formula (sum) the result will be 50
and if i hide cell A1 in the formula (sum) the result will not changed
so i want formula not collect the hide cell
I hope my explanation clear
Thank you

[Aussiebear]

Try the following

=Subtotal(109,A1:A5)

With all rows visible it will create a result of 50, yet if you hide a row it will only sum the visible rows.

Note also that when you unhide the rows later on it will then sum the full range.

[thair younis]

Hi Aussiebear
Thank you it working well
but i have one question what mean this part below
in red color
=Subtotal(109,A1:A5)

[Bob Phillips]

It is explained in help for SubTotal.

[thair younis]

Hi xld
i meant the ( 109)
can you be more expand please
Thanks

[mancubus]

http://office.microsoft.com/en-us/excel-help/subtotal-function-HP010062463.aspx

[Aussiebear]

As XLD has indicated, it is all explained in help for Subtotal. The syntax for the Subtotal function is Subtotal(function-Num, ref1,ref2, etc). The numbers 1 through to 11 includes hidden values, whereas the numbers 101 to 111 ignores hidden numbers.

So if you really want to know about what the numbers actually do..... go to the subtotal function within Excel and read.

[thair younis]

As XLD has indicated, it is all explained in help for Subtotal. The syntax for the Subtotal function is Subtotal(function-Num, ref1,ref2, etc). The numbers 1 through to 11 includes hidden values, whereas the numbers 101 to 111 ignores hidden numbers.

So if you really want to know about what the numbers actually do..... go to the subtotal function within Excel and read.

Hi Aussiebear

but I want know what different between (109) and (9) are they same function
or different


http://office.microsoft.com/en-us/excel-help/subtotal-function-HP010062463.aspx
Thank you mancubus for this link

Thanks all

[Bob Phillips]

For goodness sake man, don't be lazy, look at help. It tells you there, I can see it on the screen in front of me now, and it tells you on that link above.

[thair younis]

Ok xld