count cells based on the row and column headings in excel

[trenteb]

Hi, I am attempting to count the number of times a certain criteria occur based on varying column & row headings. the same Row & Column headings appear multiple times in the data set. I have tried countifs with index & match functions but have been unable to get it to work. Hoping someone can assist.

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, it would help greatly if you provided sample data (& expected results) with XL2BB so helpers don't have to manually type out sample data to test with & show results.

See if this does what you want. (I coloured some manually for visual checking)

23 12 19.xlsm
	A	B	C	D	E	F	G	H	I
1		x	x	y	x	y	y	y	z
2	a	NC	d	NC	L	d	W	W	L
3	a	W	L	L	W	I	d	d	I
4	a	W	NC	L	d	d	d	L	I
5	b	d	L	W	L	NC	I	L	d
6	b	NC	W	L	d	d	d	I	I
7	c	NC	L	NC	I	I	I	I	L
8	c	I	d	I	I	W	W	d	NC
9	c	NC	d	L	L	W	L	NC	L
10	c	d	NC	NC	d	W	d	d	I
11	c	NC	I	W	NC	NC	L	d	L
12	c	L	NC	W	NC	d	I	d	W
13
14			x	y	z
15	a	W	3	2	0
16		I	0	1	2
17		NC	2	1	0
18		L	2	3	1
19		d	2	5	0
20	b	W	1	1	0
21		I	0	2	1
22		NC	1	1	0
23		L	2	2	0
24		d	2	2	1
25	c	W	0	6	1
26		I	4	5	1
27		NC	7	4	1
28		L	3	3	3
29		d	4	6	0
Counting

Cell Formulas
Range	Formula
C15:E29	C15	=SUMPRODUCT(($A$2:$A$12=LOOKUP("zz",$A$15:$A15))*($B$1:$I$1=C$14)*($B$2:$I$12=$B15))

 

[trenteb]

Thanks for the prompt reply, solution works perfectly!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.