Count amount of time a string appears

watson2012

New Member
Joined
Sep 27, 2014
Messages
1
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,

Code:
=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 ...

Code:
=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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Steph77

Board Regular
Joined
Sep 18, 2014
Messages
119
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,446
Messages
5,547,971
Members
410,820
Latest member
Prepost
Top