Skip to main content

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

S

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.

    A

    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é

    S
    Andre_Zijlstra wrote:

    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é


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

    A

    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é

    S

    @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?

    A

    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é

    S
    Andre_Zijlstra wrote:

    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é


    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.

    A

    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?

    S
    Andre_Zijlstra wrote:

    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?


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

    Regards,

    Sam

    Reply


    Most helpful members this week

    Terms & Conditions