Count Employees By Gender Between Date Range (2024)

I'm trying to find a way to count all the Male and Female employees for a given date. Below is an example of my data, it's set up with a period start and end, so if the date falls between the range it should count the Male/Female employees. I want to be able to type in the date and have the correct number pop up. If tried to do this a few different ways but at this point I'm stuck. Wanted to sleep on it but decided to register here and see if anyone could help me. I'm sure it's staring me in the face but I just can't get it.

=COUNTIFS($C$2:$C$10,"<="&$G2,$D$2:$D$10,">="&$G2,$E$2:$E$10,H$1)

Aladin Akyurek said:

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Employee ID	Employee Name	Period Start	Period End	Gender			male	female
2​	30	Dan	1/6/2016	1/12/2016	Male		1/10/2016​	1​	4​
3​	45	Karen	1/6/2016	1/12/2016	Female
4​	50	Samantha	1/6/2016	1/12/2016	Female
5​	53	Brenda	1/6/2016	1/12/2016	Female
6​	64	Tom	1/6/2016	1/12/2016	Female
7​	32	John	1/13/2016	1/19/2016	Male
8​	27	Tony	1/13/2016	1/19/2016	Male
9​	42	Mary	1/13/2016	1/19/2016	Female
10​	34	Robert	1/13/2016	1/19/2016	Male

<tbody>
</tbody>

In H2 enter and copy to I2...

=COUNTIFS($C$2:$C$10,"<="&$G2,$D$2:$D$10,">="&$G2,$E$2:$E$10,H$1)

Click to expand...

See Also

COUNT and COUNTA functions to count cells in ExcelZÄHLENWENN (Funktion) - Microsoft-Support[MS Office - 2003] How to count number of Male & Female in a column on a spreadsheetWays to count values in a worksheet

Thanks so much!

kgun12 said:

Thanks so much!

Click to expand...

You are welcome.

kgun12 said:

Some of the employees have started to work on multiple projects during a given work period, so they may get counted twice using the previous formula. Now I could probably just remove duplicated rows, but what would I need to do in order to ignore a duplicates without removing any data?

Click to expand...

kgun12 said:

Yes it would, Samantha would only be counted once, so you would only have 3.

Click to expand...

Actually 4 instead of 5 for Samantha would count once...

=SUM(IF(FREQUENCY(IF(ISNUMBER($A$2:$A$11),IF($C$2:$C$11<=$G2,IF($D$2:$D$11>=$G2,IF($E$2:$E$11=H$1,$A$2:$A$11)))),$A$2:$A$11),1))

I realize this problem has already been solved quite elegantly. However, if you’re interested in a non-array formula solution, here’s an alternative:

=SUMPRODUCT(--($G$2>=$C$2:$C$11),--($G$2<=$D$2:$D$11),--($H1=$E$2:$E$11),1/COUNTIF($A$2:$A$11,$A$2:$A$11))

Mathias Pedersen Heinze said:

I realize this problem has already been solved quite elegantly. However, if you’re interested in a non-array formula solution, here’s an alternative:

=SUMPRODUCT(--($G$2>=$C$2:$C$11),--($G$2<=$D$2:$D$11),--($H1=$E$2:$E$11),1/COUNTIF($A$2:$A$11,$A$2:$A$11))

Click to expand...

While it requires just enter for confirmation, it's an array-processing formula and not very efficient.

See: https://www.mrexcel.com/forum/excel-questions/292473-performance-formulas-unique-count.html