Subtotal and Auto-filter

[John]

I need to find a percentage of 2 columns using auto-filter. The first colum
formula is

=SUBTOTAL(9,F3:F5000)]

The second column is

=SUBTOTAL(9,G3:G5000)


Anyone wanna help...also how the formula works so I don't need to ask dumb
questions....

 

[Sheeloo]

John,
(For complete details lookup under SUB TOTAL in Excel Help)

Essentially
1. If there are other subtotals within ref1, ref2,… (or nested subtotals),
these nested subtotals are ignored to avoid double counting.
2. The SUBTOTAL function ignores any rows that are not included in the
result of a filter, no matter which function_num value you use.
3. Code 9 in Sub Total means SUM function, 1 is average, 2 is count,....

For percentage in the cell next to the cell (C11) having Subtotal (assuming
B11) enter
=B11/B$200*100
(B200 is the Grand Total cell)
Copy this formula to all cells next to sub total cells...

 

[Gord Dibben]

Assume formulas are below last data, say F5001 and G5001

In H5001 enter =F5001/G5001 or the other way round.


Gord Dibben MS Excel MVP