Countif in pivot table

[tigerzen]

Thought this would be straightforward but I can't see how to do this. Need to know how many staff from each department are over a certain amount which is cell-based. HR should be 3 etc.

Book1
	A	B	C	D	E	F	G
1	Name	Dept	Gender	Salary		Threshold	$ 50,000
2	Tom	HR	M	$ 42,883
3	Harry	Sales	M	$ 99,281
4	Ken	Admin	M	$ 45,938
5	Bert	HR	M	$ 30,579
6	Mary	HR	F	$ 64,173
7	Jane	HR	F	$ 94,951
8	Kelly	HR	F	$ 77,407
Sheet1

 

[Muhammad_Usman]

 

[tigerzen]

Hi Muhammad, thanks for your response but I was looking for a solution using a pivot table if possible.

 

[Muhammad_Usman]

Hi Muhammad, thanks for your response but I was looking for a solution using a pivot table if possible.

Yes it is possible with a addition of a column. see the attachments

 

[Muhammad_Usman]

Receip.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Sr.No	Name	Dept	Gender	Salary	Status	$50,000		Row Labels	# Staff
3	2	Tom	HR	M	42883	0			HR	3
4	3	Harry	Sales	M	99281	1			Sales	1
5	4	Ken	Admin	M	45938	0			Admin	0
6	5	Bert	HR	M	30579	0			Grand Total	4
7	6	Mary	HR	F	64173	1
8	7	Jane	HR	F	94951	1
9	8	Kelly	HR	F	77407	1
10
11
Sheet12

Cell Formulas
Range	Formula
F3:F9	F3	=IF(E3>$G$2,1,0)

 

[tigerzen]

Receip.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2	Sr.No	Name	Dept	Gender	Salary	Status	$50,000		Row Labels	# Staff
3	2	Tom	HR	M	42883	0			HR	3
4	3	Harry	Sales	M	99281	1			Sales	1
5	4	Ken	Admin	M	45938	0			Admin	0
6	5	Bert	HR	M	30579	0			Grand Total	4
7	6	Mary	HR	F	64173	1
8	7	Jane	HR	F	94951	1
9	8	Kelly	HR	F	77407	1
10
11
Sheet12

Cell Formulas
Range	Formula
F3:F9	F3	=IF(E3>$G$2,1,0)

Thanks Muhammad.