Calculate percentages with SUBTOTAL

[desert rat]

Is there a formula you can use to calculate the percentage of data that is only visible on the spreadsheet after it has been filtered?


For Example

I have a SUBTOTAL in Column A, Locations in Column B, and data in Column C


Column B Column C

Los Angeles 15

Los Angeles 10

Miami 9

New York 23

Los Angeles 7

Chicago 4

Miami 17


i then have SUMPRODUCTs to calculate

Total Wins:

>=10

<10


Is there a formula i could use to display a % for the wins >=10 and wins <10 for example if i filtered Column A using "Los Angeles"?

 

[NARAYANK991]

Hi ,


The Excel help on the SUBTOTAL function says this :

[pre]

The SUBTOTAL function ignores any rows that are not included in the result of a filter,
no matter which function_num value you use.

[/pre]
So what you want should be possible.


Narayan

 

[desert rat]

Thanks Narayan.


Think i must have been staring at this spreadsheet for too long! As soon as i read your post i realised i had the data already on the spreadsheet and just needed to do a simple SUM statement........


Cheers

 

[desert rat]

Thought i had it...but nope


this is the formula i am currently using


=E250/(SUBTOTAL(103,OFFSET(G2,ROW(G2:G236)-ROW(G2),0)))


trying to get the cells in E250 to divide by the subtotal but all i get is #DIV/0!


Any ideas?

 

[desert rat]

No need to worry i have finally solved the problem.


Pretty sure i am going abot it in the long way as i am just using a SUM to total a few other cells with SUMPRODUCT results.


Thanks for all your help it has been appreciated