Count amount of time a string appears

[watson2012]

Hi all,

I am creating a spreadsheet to record football stats of my local team, i have got most of the formulas i need so far but i am struggling with counting how many times a person has scored.

I have several pages in my file e.g Players, Cup Games, League Games

In the players page i have stats of apps etc, cup games and league games have score, lineup, scorers etc

on the players page i am trying to count how many times a player has scored this season,

=LEN('Cup Games'!AC2)-LEN(SUBSTITUTE('Cup Games'!AC2,A29,LEFT(A29,LEN(A29)-1)))

works to count for one cell where A29 is the platers name but if i try do it over the season (more than one cell) it doesnt work, this is the code i used ...

=LEN('Cup Games'!AC2:AC30)-LEN(SUBSTITUTE('Cup Games'!AC2:AC30,A29,LEFT(A29,LEN(A29)-1)))

what should my code be? obviously 1 cell may contain the players name more than once

Cheers in advance

 

[Steph77]

I don't think you can use a range like this in that function.

Could you not create a table with a column/row for each game/player using the individual cell formula for a single game and then make totals on the columns/rows?