Help me I'm going CRAZY!!! COUNTIF AND INDIRECT FORMULA?!

Danfrancozola

New Member
Joined
Sep 1, 2014
Messages
19
So basically I am creating a database of football statistics to include corners, goals, possession etc.
I am concentrating on many leagues around the world and for each team I am doing a seperate tab, so there will be many tabs.

For each team in the premier league for example, each tab is known as PL1, PL2, PL3 etc.

I need the same formula on a statistics tab for each of the teams.

Arsenal for example are known this season as PL1.

For a formula to count how many games they have won I have this formula: =COUNTIF('PL1'!$T$9:$T$28,"WIN")

When I drag down to the new cell, I need the PL1 to change to the next tab which will be PL2.


I have tried so many different formulas and I just can't seem to get it right. Someone out there must know the answer!

Thanks a million!

Danfranco.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
=COUNTIF(INDIRECT("'PL"&ROW(A1)&"'!$T$9:$T$28"),"WIN") Then copy this formula down as required.
 
Last edited:
Upvote 0
ROW(A1) would give a value of 1 as it is the first row. When you copy the formula down across the remaining data it will increase in increments of 1 for each row, so PL1 in row 1 becomes PL2 in row 2 etc. It should reference to the current tab. This formula worked fine for me. What results are you getting? Are you sure the wins in cells T9 to T28 are actually entered as WIN?
 
Last edited:
Upvote 0
So my stats page is on one tab, which I have cell references to PL1, PL2, PL3 etc.

I need the formula on this sheet to show the calculation when linked to these other tabs. When I drag it down I need it to change between these tabs so I dont have to do it manually.

I'm just getting 0 as an answer all of the time?
 
Upvote 0
Let's say you have PL1, PL2 and so on in cells A1, A2 etc on the Stats sheet. You can use:

=COUNTIF(INDIRECT("'"&$A1&"'!$T$9:$T$28"),"WIN")
in another cell on the Stats sheet, which will calculate as =COUNTIF('PL1'!$T$9:$T$28,"WIN")
If you copy that down it will become:
=COUNTIF(INDIRECT("'"&$A2&"'!$T$9:$T$28"),"WIN")
which is calculated as =COUNTIF('PL2'!$T$9:$T$28,"WIN")
and so on.
 
Upvote 0
PL1 and PL2 are the names of the worksheets that I need to change.

I have put that formula in to my stats page and its coming back #REF.

Im so confused :(
 
Upvote 0
1. check that you do not have spaces either side of PL1 or PL2 in your sheet names.
2. get one team working first and then the others will follow.
 
Upvote 0
I have put it in my stats page which is called ht result. I have put it in the cell for arsenal (L7) and tried to link it to PL1. It keeps coming up with an error. PL1 shows all of the stats for arsenal for each of their games.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top