General help with Filters and column recalculation...

[Onceageordie]

Hi,

I am analysing some football (soccer!) data and wish to use filters to narrow down certain parameters. Before filtering I have a number of columns which show running totals of selections, winners, £ Balance & % strike rate.

When I then use a filter the totals columns obviously still show the original values from the "Full" data set. How can I get them to re-calculate only using the filtered results?

I have enclosed a sample sheet showing a small selection of the data I am working with.

Is this where I need to look at pivot tables instead of filtering - not a subject I know anything about!

Cheers

Dale

[canapone]

Hi,

subtotal instead of sum could be helpful

Example:

=SUBTOTAL(9,O2:O45)

Hope it helps

[Onceageordie]

ok,

Managed to use SUBTOTAL(9 to sum the "£ Return" column, and SUBTOTAL(2 to count the rows in "Stake", but I also want to count the number of rows in "W?" that contain "Y". There doesn't seem to be a COUNTIF option in the SUBTOTAL function.

Any more suggestions?

[canapone]

Hi,

thanks for your kind feedback

Please Login or Register  to view this content.

Please see in the attached file the "Green" SUMPRODUCT.

Regards

[Onceageordie]

All sorted, many thanks.

[canapone]

Hi, you're welcome.

Thanks again for feedback.