Countifs with multiple horizontal and vertical criteria

[mobile73]

Hi, hoping someone can help. Searched everywhere but can't find a solution. I want to be able to count number of entries matching multiple criteria. In example below, I would want to count number of non zero entries in Q1 1718 for Director AB and Manager A (so return a value of 2). Actual spreadsheet has thousands of rows so looking for a good solution. I know I can use sumproduct to sum the cells but I want to count the number of non zero entries.

Any help greatly appreciated, thanks.

M73

Book1
	A	B	C	D	E	F	G	H	I	J	K
1				1718	1718	1718	1718	1819	1819	1819	1819
2				Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
3	Director	Manager	Name
4	AB	A	G	947	257	789	681	997	22	893	853
5	AB	A	H	760	216	753	478	52	467	861	532
6	AB	B	I	157	934	286	581	70	734	43	550
7	BC	C	J	190		18	327	107	525	359	265
8	BC	D	K	754	855	60	768	338		816	242
9	BC	D	L	290	661	215	966	629	142	273	896
10	CD	E	M	179	505	88	368	329	777	563	923
11	CD	E	N	29	923	213		340	931		647
12	CD	E	O	96	978	756	303	789	434	115	108
13	CD	F	P	777	841	538	363	87	560	490	866
Sheet1

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF($A$4:$A$13="AB",IF($B$4:$B$13="A",IF(($D$1:$K$1=1718)*($D$2:$K$2="Q1"),IF(ISNUMBER($D$4:$K$13),1)))))

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1				1718	1718	1718	1718	1819	1819	1819	1819
2				Q1	Q2	Q3	Q4	Q1	Q2	Q3	Q4
3	Director	Manager	Name
4	AB	A	G	947	257	789	681	997	22	893	853
5	AB	A	H	760	216	753	478	52	467	861	532
6	AB	B	I	157	934	286	581	70	734	43	550
7	BC	C	J	190		18	327	107	525	359	265
8	BC	D	K	754	855	60	768	338		816	242
9	BC	D	L	290	661	215	966	629	142	273	896
10	CD	E	M	179	505	88	368	329	777	563	923
11	CD	E	N	29	923	213		340	931		647
12	CD	E	O	96	978	756	303	789	434	115	108
13	CD	F	P	777	841	538	363	87	560	490	866
14
15
16			AB	A	1718	Q1	2
Sheet2

Cell Formulas
Range	Formula
G16	=SUMPRODUCT(--($A$4:$A$13=C16)*--($B$4:$B$13=D16)*--($D$1:$K$1=E16)*--($D$2:$K$2=F16)*--($D$4:$K$13<>""))

(mixing -- and * with sumproduct isn't always needed, but I was too lazy to test without)

 

[mobile73]

Thanks both - these both give what I need. Really appreciate the quick replies.

 

[Aladin Akyurek]

Thanks both - these both give what I need. Really appreciate the quick replies.

You are welcome.