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

#### Danfrancozola

##### New Member
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
=COUNTIF(INDIRECT("'PL"&ROW(A1)&"'!\$T\$9:\$T\$28"),"WIN") Then copy this formula down as required.

Last edited:
It's not working. What does A1 mean? What tab does that cell reference to?

It's not working. What does A1 mean? What tab does that cell reference to??

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

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.

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

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.

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.

Replies
11
Views
383
Replies
2
Views
437
Replies
2
Views
472
Replies
5
Views
424
Replies
3
Views
310

1,221,216
Messages
6,158,585
Members
451,501
Latest member
andysacko

### 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.

### Which adblocker are you using?

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

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