Count Value based on Matching Criteria

[aspence]

I am trying to count up the number of times A or B appear based on the corresponding unit type from Column I. I am having trouble making the value from column I search for a match of that value in column B. If they match, I need to add up the number of times A appears (in column J) or B appears (in column K).

Scheme.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1		Unit Type	1st Floor	Second Floor	Third Floor	Fourth Floor	Fifth Floor		Unit Type List	Scheme A Count	Scheme B Count
2	1 Stack	B1	LEASING	B	A	B	A		A1
3	2 Stack	B3	CLUB	B	A	B	A		A2
4	3 Stack	B1	LEASING	A	B	A	B		A3
5	4 Stack	B2	GAME	A	B	A	B		A4
6	5 Stack	B4	FITNESS	FITNESS	A	B	DECK		A5
7	6 Stack	A6	YOGA	A	B	A	B		A6
8	7 Stack	A4	A	B	A	B	A		A7
9	8 Stack	A6	MICRO	B	A	B	A		A8
10	9 Stack	A4	B	A	B	A	B		B1
11	10 Stack	A2	A	B	A	B	A		B2
12	11 Stack	A7	B	A	B	A	B		B3
13	12 Stack	A2	B	A	B	A	B		B4
14	13 Stack	B6	A	B	A	B	A		B5
15	14 Stack	A3	A	B	A	B	A		B6
16	15 Stack	B5	B	A	B	A	B		S1
17	16 Stack	A3	B	A	B	A	B			172	173
18	17 Stack	A4	A	B	A	B	A
19	18 Stack	B3	POOL	B	A	B	A
20	19 Stack	S1	B	A	B	A	B
21	20 Stack	S1	A	B	A	B	A
22	21 Stack	A3	B	A	B	A	B
23	22 Stack	S1	B	A	B	A	B
24	23 Stack	S1	A	B	A	B	A
Sheet2

Cell Formulas
Range	Formula
J17	J17	=COUNTIF(C2:G75,"A")
K17	K17	=COUNTIF(C2:G75,"B")

I am apparently not using the COUNT function correctly, or am missing something entirely.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

One option that will work with most versions

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		Unit Type	1st Floor	Second Floor	Third Floor	Fourth Floor	Fifth Floor		Unit Type List	Scheme A Count	Scheme B Count
2	1 Stack	B1	LEASING	B	A	B	A		A1	0	0
3	2 Stack	B3	CLUB	B	A	B	A		A2	5	5
4	3 Stack	B1	LEASING	A	B	A	B		A3	7	8
5	4 Stack	B2	GAME	A	B	A	B		A4	8	7
6	5 Stack	B4	FITNESS	FITNESS	A	B	DECK		A5	0	0
7	6 Stack	A6	YOGA	A	B	A	B		A6	4	4
8	7 Stack	A4	A	B	A	B	A		A7	2	3
9	8 Stack	A6	MICRO	B	A	B	A		A8	0	0
10	9 Stack	A4	B	A	B	A	B		B1	4	4
11	10 Stack	A2	A	B	A	B	A		B2	2	2
12	11 Stack	A7	B	A	B	A	B		B3	4	4
13	12 Stack	A2	B	A	B	A	B		B4	1	1
14	13 Stack	B6	A	B	A	B	A		B5	2	3
15	14 Stack	A3	A	B	A	B	A		B6	3	2
16	15 Stack	B5	B	A	B	A	B		S1	10	10
17	16 Stack	A3	B	A	B	A	B			52	53
18	17 Stack	A4	A	B	A	B	A
19	18 Stack	B3	POOL	B	A	B	A
20	19 Stack	S1	B	A	B	A	B
21	20 Stack	S1	A	B	A	B	A
22	21 Stack	A3	B	A	B	A	B
23	22 Stack	S1	B	A	B	A	B
24	23 Stack	S1	A	B	A	B	A
Master

Cell Formulas
Range	Formula
J2:J16	J2	=SUMPRODUCT(($B$2:$B$24=I2)*($C$2:$G$24="A"))
K2:K16	K2	=SUMPRODUCT(($B$2:$B$24=I2)*($C$2:$G$24="B"))
J17	J17	=COUNTIF(C2:G75,"A")
K17	K17	=COUNTIF(C2:G75,"B")