Can I count how many times something appear in a range of entries?

[Sam_McDonnell]

I have a sheet called fixtures and it is filtered into seasons, each entry represents one match and has players listed that played in a match. I want to be able to count how many times a player appeared in a each season. Any ideas? The seasons are currently identified by a ‘single-select’, and each fixture is numbered individually regardless of season.

[Andre_Zijlstra]

Hi @Sam_McDonnell,

Are the players linked to - and listed in - another table? If that’s the case, that will make it a lot easier.
Greetz,
André

[Sam_McDonnell]

Hi @Andre_Zijlstra , yes all the players link to another table.

[Andre_Zijlstra]

Hi @Sam_McDonnell,

I tested 2 tables (Fixtures and Players) of an imaginary u11 team. 7 players are on the Staring VII and 3 are subs. It would look like this:

Fixtures:

Players:

Count fields give you the number a player is selected in Starting VII and Subs.
A formula field adds up both the Count fields. And added to this, at the bottom you see numbers that:

1. you should be able to divide by 7
2. you should be able to divide by 3
3. you should be able to divede by 10

Regards,
André

[Sam_McDonnell]

@Andre_Zijlstra thanks, that’s very similar to what I have. The main difference is I have 13 seasons of fixtures with each season identified by a single select field. I’m looking to count players appearances by season, any ideas?

[Andre_Zijlstra]

The easiest way would probably be to create a formula field per season / for the starting players and a formula field per season for the subs. Something like this:

Of course you would need a field to identify the season.

Formula for the start squad:

In the table where you find the players, you need Rollup fields (per season, start, sub).

For the starting players:

For the subs:

And that will like like this:

Of course you may choose the option to create a view per season. In that view you can hide the fields that contain data of other seasons.

Hopefully that will work for you!

Greetz,
André

[Sam_McDonnell]

Thanks @Andre_Zijlstra, that looks like the route I should be pursuing. One difference is I have 11 fields for the different starters rather than linking them all in the same field. I’m sure there’s a work around for this. Thanks again for your help.

[Andre_Zijlstra]

Are those 11 fields related to the positions on the field? In other words, do you bring a sub into a position or just one player for another?

[Sam_McDonnell]

I just have three additional fields for the subs, and discount any subs who were not brought on.

Regards,

Sam