How to filter source data and use only that filtered data in results/formula's

[SoccerGirll]

Hi,

I have a document with statistics for my national soccer league from the last 20 years.
They contain, teams, scores, goals, cards, corners.

With those stats, I used some formulas to show me how many wins, corners, goals, goal differences,... They all have a separate formula, and are on a second page

I made this all based on the stats I have for the current season.
Now my question is the following:

If I make the source file for my stats, all the stats from the past years, is there a way my formulas will still work If I filter in that file.
eg. i wanna know the stats for this season en the one before, my results (the ones on the second page) still show the data for all the seasons; and not just those from the last 2 seasons.

Also, I can't make a seperate page with formula's for each season, seeing as I sometimes want 2 or more season stats at once.

Thanks in advance to any advice you can give me.

[Roel Jongman]

Have a look at the SUBTOTAL() function
https://support.office.com/en-us/art...0-e478765b9939

it can handle filtered area's to only count visible cells in the range.

[SoccerGirll]

I figured SUBTOTAL() was only for sums, I see I can use it for more functions, but the formulas I use are COUNTIF
Is there any way to do it when I'm using the COUNTIF formulas in my result?

[Roel Jongman]

No countif is not able to work with visible only but if you set the subtotal function to count and only visible cells then it will act the same as a countif but also handle only visible rows.

[SoccerGirll]

Okay, Thanks for the info, this will help me along.
Maybe you could help me with this,
in the document I have in column C the home team and in column D the away team. Is there any way to filter so that I can see all games from 1 team?
Meaning; show all the games 1 team plays both away and at home?

currently I'm using this formula to get all the row numbers in which a certain team stands, and I fill out the rest of the columns based on the row number. But I was wondering whether there was a simpler way.

=KLEINSTE(ALS('2018-2019'!D:E=$D$1;RIJ('2018-2019'!D:E));RIJ('2018-2019'!1:1))

KLEINSTE = SMALLEST
ALS = IF
RIJ = ROW

[JeteMc]

Since you have to array enter the SMALL(IF... formula, you may want to look into the AGGREGATE function. AGGREGATE(15,6,... can be used to yield the same results without array entering.
https://support.office.com/en-us/art...6-E19993FA26DF
Let us know if you have any questions.