Countif question that looks at a tab reference

[arisomar]

Hi All,

I am using this to count initials in a tab:

=COUNTIF('1'!$B$5:$AK$70,"PT")

There are 100 tabs all named 1-100, therefore the next formula would be:

=COUNTIF('2'!$B$5:$AK$70,"PT")

Is there are quick way to have the tab reference change sequentially without having to F2 in every tab and manually change it?

 

[HighAndWilder]

Hi All,

I am using this to count initials in a tab:

=COUNTIF('1'!$B$5:$AK$70,"PT")

There are 100 tabs all named 1-100, therefore the next formula would be:

=COUNTIF('2'!$B$5:$AK$70,"PT")

Is there are quick way to have the tab reference change sequentially without having to F2 in every tab and manually change it?

You can use the INDIRECT function.

Display and add up Grams Kilograms.xlsx
	C	D	E	F
1
2
3		1	269
4		2	365
5		3	306
6
7
8
9
Sheet1

Cell Formulas
Range	Formula
E3:E5	E3	=COUNTIF(INDIRECT($D3 & "!$B$5:$AK$70"),"PT")

 

[arisomar]

Perfect. Thank you!!