Indirect formula based off cell value on current sheet

[ItalianPlatinum]

I wasn't getting much action on a prior thread I posted Find last row of sheet based off cell value to reference the sheet name. So posting again with little more troubleshooting I have done as well as simplifying the ask. Either formula or VBA - how can I set a formulas range to be dynamic of the Last row of a given sheet. I.e below. I tried creating a helper column in column D but can't get a way to in cooperate. I am applying these formulas via VBA if it matters. The below is just a drafted version of a much larger data set over 1million rows and potential of 25 sheets hence need it to be dynamic.

Book2
	A	B	C	D
1	List	Eligible to Consolidate	Consolidated	Total Consolidated
2	G23456789	0	Similar Formula	7
3	G23456780	0	Similar Formula	7
4	G23456781	0	Similar Formula	7
5	G23456782	0	Similar Formula	7
6	H23456782	0	Similar Formula	6
7	H23456781	0	Similar Formula	6
8	H23456783	0	Similar Formula	6
9	I23456781	0	Similar Formula	5
10	I23456782	0	Similar Formula	5
11	I23456783	0	Similar Formula	5
12	I23456784	0	Similar Formula	5
13	I23456785	0	Similar Formula	5
14	I23456786	0	Similar Formula	5
Summary

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(COUNTIFS(INDIRECT(LEFT(A2,1)&"!$D$2:$D$7"),A2,INDIRECT(LEFT(A2,1)&"!$Q$2#"),">1"),0)
B6:B14	B6	=IFERROR(COUNTIFS(INDIRECT(LEFT(A6,1)&"!$D$2:$D$9154"),A6,INDIRECT(LEFT(A6,1)&"!$Q$2#"),">1"),0)
D2:D14	D2	=COUNTA(INDIRECT(LEFT(A2,1)&"!$D$2:$D$9154"))

 

[Alex Blakenburg]

Although whole column referencing is frowned upon, the documentation indicates that CountIf (and its siblings) are Used Range aware and I have to assume that applies to the newer CountIfs versions as well.
So asuming your formulas works on the limited range, try this.

=IFERROR(COUNTIFS(INDIRECT(LEFT(A2,1)&"!$D:$D"),A2,INDIRECT(LEFT(A2,1)&"!$Q:$Q"),">1"),0)

 

[ItalianPlatinum]

Do you think I will experience performance issues using full columns? That was my fear that led me down the path of using a last row

 

[Alex Blakenburg]

For the Countifs etc functions using the full columns should make very little difference