=COUNTIFS with two labels

[DPChristman]

I am trying to calculate the number of time a persons initials appear for a specific location.
The search range is COL I-ROW 5 through COL CD-ROW 204 in Sheet 1 of the spreadsheet, and the initials for the users appear on sheet 2 of the spreadsheet.
Using countif, I can find the number of times a specific user's initials appear in the range overall.
However, what I want to do is identify the number of times the initials appear for a Tier 1 and Tier 2 location.
I would think the formulas necessary would be =COUNTIFS(Sheet1!I5:CD204,A2,Sheet1!G:G,"Tier 1") and =COUNTIFS(Sheet1!I5:CD204,A2,Sheet1!G:G,"Tier 2"), but that is not working.
What am I missing?

 

[Peter_SSs]

You will generally get faster and better responses if you use XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. If you have trouble with XL2BB review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of theXL2BB Instructions page linked above.

Here is a small example to see if it is doing what you want if so, you should be able to adapt it to your actual layout & ranges.

DPChristian.xlsm
	G	H	I	J	K
5	Tier 2	BF	CP
6	Tier 2	XY
7	Tier 1	CP	AL
8	Tier 1
9	Tier 2		BR	CP
10	Tier 1	AL
Sheet1

DPChristian.xlsm
	A	B
1
2	AL	2
3	BF	0
4	CP	1
5
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=BYROW(A2:A4,LAMBDA(r,LET(I,TOCOL(IF(Sheet1!G5:G10="Tier 1",Sheet1!H5:J10,1/0),2),IFNA(ROWS(FILTER(I,I=r,NA())),0))))
Dynamic array formulas.

 

[DanteAmor]

Let's use headings on sheet2 to do the counting, for example:

Dante Amor
	A	B	C
1	Init	Tier 1	Tier 2
2	AL	1	2
3	BF	1	1
4	CP	1	1
5	DB	3	0
Sheet2

Cell Formulas
Range	Formula
B2:C5	B2	=SUMPRODUCT((Sheet1!$I$5:$CD$204=$A2)*(Sheet1!$G$5:$G$204=B$1))

Dante Amor
	A					F	G	H	I	J	K	L	M	N	O
1
2
3
4							Target
5							Tier 2		AL
6							Tier 2			BF
7							Tier 2				CP	AL
8							Tier 1		DB			AL	BF	DB
9							Tier 1			DB				CP
10
Sheet1

 

[DPChristman]

Let's use headings on sheet2 to do the counting, for example:

Dante Amor
	A	B	C
1	Init	Tier 1	Tier 2
2	AL	1	2
3	BF	1	1
4	CP	1	1
5	DB	3	0
Sheet2

Cell Formulas
Range	Formula
B2:C5	B2	=SUMPRODUCT((Sheet1!$I$5:$CD$204=$A2)*(Sheet1!$G$5:$G$204=B$1))

Dante Amor
	A					F	G	H	I	J	K	L	M	N	O
1
2
3
4							Target
5							Tier 2		AL
6							Tier 2			BF
7							Tier 2				CP	AL
8							Tier 1		DB			AL	BF	DB
9							Tier 1			DB				CP
10
Sheet1

=SUMPRODUCT worked perfect! Thanks!