# Help with Formula to count game points

Copper Contributor

# Help with Formula to count game points

Hi, I'm a soccer mum with little excel smarts. I'm collecting each game from the team who were the top 3 players for the game and assigning them 3 points for best, 2 points for 2nd best and 1 point for 3rd best. At the end of the season this goes towards awards.

I have set up a table with drop down boxes and I ask each girl who they are allocating the 3 pints to, the 2 pints to and the 1 point.

What I need now is a way to calculate the points each player got each game.

This is what I'm stuck figuring out.

If I can figure this out I can apply this formula to all the subsequent games.

10 Replies

# Re: Help with Formula to count game points

More complicated formulas are possible, but I suggest you paste this formula in for Eadie (cell B24) and copy it down:

``=3 * COUNTIF(\$B\$3:\$B\$21, A24) + 2 * COUNTIF(\$C\$3:\$C\$21, A24) + 1 * COUNTIF(\$D\$3:\$D\$21, A24)``

# Re: Help with Formula to count game points

@CGallagherInner Another option is the SUMPRODUCT function. Input the following formula in cell B24 and copy down:

``=SUMPRODUCT((\$B\$3:\$D\$21=A24)*\$B\$2:\$D\$2)``

# Re: Help with Formula to count game points

Thanks@SnowMan55 this formula worked really well.

If I was to create a TOTAL tab that looked up the value of the players

total cell in each game tab, what would that formula be? I plan to create a new game tab each week but would love this to auto calculate in the TOTAL tab.

Eg; Calculate points for Eadie in B24 across ALL tabs (game) to go into cell B4 in the TOTAL tab (for all players). And how would I adjust the formula each week I add a new game Tab?

# Re: Help with Formula to count game points

It's better to insert empty sheet before one with first game, another empty sheet after the latest game. Let name them start and end accordingly. Each new game tab is inserted in between. The rest depends do you have exactly the same players for all games; are their names in the same rows for each game or not; and on which Excel version you are.

In simplest case when the same player name is in the same row for each game in B4 for Total sheet could be

``=SUM(start:end!B24)``

and drag it down.

# Re: Help with Formula to count game points

yes thats correct, they are the same players in the same cells on each tab.
I tried your way (I think I did), but it's not working.

# Re: Help with Formula to count game points

It looks like you are on Google Sheets. Perhaps it work differently, not sure. In any case it's better to discuss an issue on the sample file. In the attached one I changed the layout, but the idea is the same

# Re: Help with Formula to count game points

Thansk for your time, I already have the total for each game calculated, what I'm trying to do then is to look up the totals on each game tab and summarise it in the front TOTAL sheet for an easy to see consolidation of points occurring each game - So I'm after that formula for the first tab https://docs.google.com/spreadsheets/d/1MjXf5PD_Y1rtSWcO27Y2Ya-27Vnghb6Z/edit?usp=sharing&ouid=11405...

# Re: Help with Formula to count game points

Unfortunately I can't open the file, please share it with everyone

# Re: Help with Formula to count game points

done - thanks for helping me so much

# Re: Help with Formula to count game points

It seems that Google Sheets does not include the ability to reference cells across a range of sheets, as is discussed here: How to Reference a Single Cell from Multiple Sheets

So an alternative is to:

1. List the remaining sheets across row 3.
2. Use formulas in the cells beneath the sheet names, such as this formula for cell D4:

``=INDIRECT( "'" & D\$3 & "'!B" & (20+ROW()) )​``

• Use formulas with a SUM function in column B, such as this formula for cell B4:

``=SUM(D4:Z4)​``

(I included extra columns to handle future games.)