disregarding hidden cells

[SalamanderSam14]

im trying to use the subtotal formula so that it will not count any hidden cells. Loking online the formula should

=sbutotal(109,range)

however when i try this it errors out and shows #value in the cell.

Any suggestions would be great

[bebo021999]

try to correct:
=SUBTOTAL(109,RANGE)

[SalamanderSam14]

yh sorry thats what i meant bad typing on my behalf

[martindwilson]

that is correct the way you are doing it
http://office.microsoft.com/en-us/ex...005209288.aspx but if you have excel <2003 it gives #value
so for excel 97 and 2000 it is subtotal(9,range)

[SalamanderSam14]

ive got the subtotal to to work using subtotal(9,range) however this still includes the value of hidden cells. subtotal(109,range) should only total the values that are visible, i however cant get this to work

[OnErrorGoto0]

Can you post a sample? It should work in 2003 and upwards.

[SalamanderSam14]

This is a quick example

[bebo021999]

This is a quick example

I could not find any mistake here. There is no hidden cell. If any, this formula works properly.

[SalamanderSam14]

just checked and im using mocrosoft excel 2002 on SP3 is there any suggestions for a formula i can use to get the same results

[OnErrorGoto0]

Are you definitely using Excel 2003? (I can see that the formula did return #VALUE! for you)

[SalamanderSam14]

no sorry its 2002, i was being optimistic

[OnErrorGoto0]

Then you would need code. Perhaps

Please Login or Register  to view this content.

Note: I do not believe that Excel 2002 or earlier cause a recalculation when you hide a row, so the formula is dependent on other factors causing the workbook to recalculate.

[SalamanderSam14]

how would i add this code to a certain cell, and which line affects the range that is subtotalled

[OnErrorGoto0]

You add that code to a normal module in your workbook and then in the cell you use:
=SumVis(A1:A5)
for example.

[martindwilson]

subtotal(9,range), works fine on autofilterd cells,how are you hiding cells?

[SalamanderSam14]

yh im hiding cells

[SalamanderSam14]

You add that code to a normal module in your workbook and then in the cell you use:
=SumVis(A1:A5)
for example.

ive tried this on a sample workbook but it errors and says #NAME?

[OnErrorGoto0]

Then either you have macros disabled or the code is not in a normal module in the same workbook.