Indirect Formula help

[ItalianPlatinum]

I am having some difficulty putting an indirect formula to use that I feel should be easy to do. I have in column A my list, and I need to countif a column on the sheet respective to the list. Each sheet is the first character in column A. For example sheet G in my example. The one below is is A6579001, so the formula would need to reference sheet "A." I am attempting to make it dynamic but having trouble. even if I drag my column C down "A3" stays stagnant. So I cannot even succeed breaking this into parts. Any help would be nice I tried looking here Indirect and that kicked me off.

	COUNTIFS(G!$D$2:$D$2217,A3,G!$Q$2#,">1")	LEFT(INDIRECT("A3"),1)&"!"
List	Formula	Indirect + Left Formula
G12345678	0​	G!

 

[dreid1011]

Why do you need INDIRECT there? The way you are using it, is the same as LEFT(A3,1).

Perhaps your intent was something like this:

Book1
	A	B
1	G123456789	99
Sheet1

Cell Formulas
Range	Formula
B1	B1	=INDIRECT(LEFT(A1,1)&"!A5")

Book1
	A
1
2
3
4
5	99
G

 

[ItalianPlatinum]

My intend is for column B to have the formula that I can drag down to dynamically update based off of column A left(XXX,1). I was only using a helper column to try to get that darn indirect to function (that was column C). Like in your example how would you incorporate that COUNTIFs in there?

COUNTIFS(G!$D$2:$D$2217,A3,G!$Q$2#,">1")

 

[dreid1011]

Like this:

Book1
	A	B	C
1	G123456789	99	3
Sheet1

Cell Formulas
Range	Formula
B1	B1	=INDIRECT(LEFT(A1,1)&"!A5")
C1	C1	=COUNTIFS(INDIRECT(LEFT(A1,1)&"!A1:A10"),99)

Book1
	A
1	55
2	65
3	75
4	85
5	99
6	99
7	99
8	105
9	115
10	125
G

 

[dreid1011]

Something like this perhaps:

=COUNTIFS(INDIRECT(LEFT(A3,1)&"!$D$2:$D$2217",A3,INDIRECT(LEFT(A3,1)&"!$Q$2#",">1")

 

[ItalianPlatinum]

=COUNTIFS(INDIRECT(LEFT(A3,1)&"!$D$2:$D$2217",A3,INDIRECT(LEFT(A3,1)&"!$Q$2#",">1")

hmm did that work for you its kicking an error for me?

 

[dreid1011]

Opps, my apologies:

=COUNTIFS(INDIRECT(LEFT(A3,1)&"!$D$2:$D$2217"),A3,INDIRECT(LEFT(A3,1)&"!$Q$2#"),">1")

 

[ItalianPlatinum]

Thanks I am going to mark this as answered but will need to makea new post seeing I need to set the range based off the last row of that respective sheet