Count total number of Applicants for a specific job position within a given date range.

[myletterboxnp]

Hi there,

Sheet1:
A2:A100 =name of applicants
C2:C100 =Position candidates applied for
E2:E100 =Date candidates applied for the job

Sheet 2:
B19 =start date (eg. 5/1/2022)
B20 = end date (eg. 5/31/2022
B2:B100 has different job positions. (Eg. B2=Massage Therapist, Guest Service Agent,.....)

After I enter a date range in B19 and B20, what should be the formula let's say in G2 cell to count the total number of candidates who had applied for a particular position within that date range for that particular position?

What should be the formula in G2 cell?

Thank you for your help.
AR

 

[etaf]

how about
=countifs(Sheet1!$C$2:$C$100 , B2 , Sheet1!$E$2:$E4100 , ">="&B19, Sheet1!$E$2:$E4100 , "<="&B20)
copy down
and will give a count for each job type in column B2 to B100 on sheet2

 

[myletterboxnp]

The above formula is giving an error message "A value used in the formula is of a wrong data type".

 

[etaf]

sorry missed type 4 instead of $
=countifs(Sheet1!$C$2:$C$100 , B2 , Sheet1!$E$2:$E$100 , ">="&$B$19, Sheet1!$E$2:$E$100 , "<="&$B$20)

Sheet1

Book12
	A	B	C	D	E
1	NAME		JOB		DATE
2	Name1		jobtype1		2/1/22
3	Name2		jobtype2		2/2/22
4	Name3		jobtype1		2/3/22
5	Name4		jobtype2		2/4/22
6	Name5		jobtype1		2/5/22
7	Name6		jobtype2		2/6/22
8	Name7		jobtype1		2/7/22
9	Name8		jobtype2		2/8/22
10	Name9		jobtype1		2/9/22
11	Name10		jobtype2		2/10/22
12
Sheet1

Sheet2

Book12
	A	B	C
1		JOB	COUNT
2		jobtype1	5
3		jobtype2	3
4		jobtype3	2
5		jobtype4	0
6		jobtype5	0
7		jobtype6	0
8		jobtype7	0
9		jobtype8	0
10		jobtype9	0
11		jobtype10	0
12		jobtype11	0
13		jobtype12	0
14
15
16
17
18
19	Start Date	2/1/22
20	End Date	2/10/22
Sheet2

Cell Formulas
Range	Formula
C2:C13	C2	=COUNTIFS(Sheet1!$C$2:$C$100,B2, Sheet1!$E$2:$E$100, ">="&B$19, Sheet1!$E$2:$E$100, "<="&B$20)

 

[myletterboxnp]

It works now, thank you! One more question - if, in sheet1, column F2:F100 has either 0 or 1 (0 representing candidate was rejected and 1representating candidate forwarded to second round), what should be the formula in Sheet 2 Column H2 (HR Rejected/ Passed) for those same positions? So instead of counting the dates, we need to now be able to count the 0 or 1s. What formula should be given to count 0 or 1 instead of date?

Thank you again,
AR

 

[etaf]

is that within the same date range - or just overall

if with the same date range
=COUNTIFS(Sheet1!$C$2:$C$100,B2, Sheet1!$E$2:$E$100, ">="&B$19, Sheet1!$E$2:$E$100, "<="&B$20)
then add the criteria for
Rejected within date range
=COUNTIFS(Sheet1!$C$2:$C$100,B2, Sheet1!$E$2:$E$100, ">="&B$19, Sheet1!$E$2:$E$100, "<="&B$20, $H$2:$H$100, 0 )
Forward within date range
=COUNTIFS(Sheet1!$C$2:$C$100,B2, Sheet1!$E$2:$E$100, ">="&B$19, Sheet1!$E$2:$E$100, "<="&B$20, $H$2:$H$100, 1 )

Ignoring dates
Rejected
=COUNTIFS(Sheet1!$C$2:$C$100,B2, $H$2:$H$100, 0 )
Forward
=COUNTIFS(Sheet1!$C$2:$C$100,B2, $H$2:$H$100, 1 )

 

[myletterboxnp]

It worked perfectly. Thank you so so much for your help.

 

[etaf]

you are welcome