Help with a count statement for multiple criterion

[dataAnalystB]

I am working with a very large data set where I need to be able to count the number of individuals for a particular position based on either their hired date or termed date. With smaller data sets, I can just sort by hired or termed date, then position, and manually count by selecting the group, but I feel there has to be a more efficient way of doing this can clicking and dragging. Not every line of the data set has a hired date, and of course some of those with hired dates do not have a termination date.

I would appreciate any suggestions on how to write a COUNTIFS statement that looks first at the position and then either Hired or Terminated to deliver the count for that position.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Hired[/TD]
[TD]Termed[/TD]
[TD]Position[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Position A[/TD]
[/TR]
[TR]
[TD]1/15/2013[/TD]
[TD]8/09/2013[/TD]
[TD]Position A[/TD]
[/TR]
[TR]
[TD]7/17/2011[/TD]
[TD]12/13/2013[/TD]
[TD]Position A[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Position B[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Position B[/TD]
[/TR]
[TR]
[TD]8/19/2013[/TD]
[TD][/TD]
[TD]Position B[/TD]
[/TR]
[TR]
[TD]1/16/2013[/TD]
[TD]4/12/2013[/TD]
[TD]Position B[/TD]
[/TR]
[TR]
[TD]3/20/2014[/TD]
[TD][/TD]
[TD]Position B[/TD]
[/TR]
[TR]
[TD]5/22/2010[/TD]
[TD]4/13/2014[/TD]
[TD]Position B[/TD]
[/TR]
[TR]
[TD]7/23/2012[/TD]
[TD][/TD]
[TD]Position C[/TD]
[/TR]
[TR]
[TD]8/23/2012[/TD]
[TD]7/15/2013[/TD]
[TD]Position C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Position C[/TD]
[/TR]
[TR]
[TD]9/10/2009[/TD]
[TD]3/25/2014[/TD]
[TD]Position C[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Position C[/TD]
[/TR]
</tbody>[/TABLE]




Thank you!

 

[Comfy]

Would you like the results based on set date i.e. count of person in employment as of today's date?

E.G. Person A would be Zero as 1 has not been hired yet and the other two have been terminated...

 

[BrianMH]

I'm sure I'm making this more complex than it needs to be.

Excel Workbook
	A	B	C	D
1	Hired	Termed	Position
2			Position A	2
3		08/09/2013	Position A	2
4	7/17/2011	12/13/2013	Position A	2
5			Position B	4
6			Position B	4
7	8/19/2013		Position B	4
8	1/16/2013	04/12/2013	Position B	4
9	3/20/2014		Position B	4
10	5/22/2010	4/13/2014	Position B	4
11	7/23/2012		Position C	3
12	8/23/2012	7/15/2013	Position C	3
13			Position C	3
14	09/10/2009	3/25/2014	Position C	3
15			Position C	3
Sheet1

 

[Comfy]

Tried to do it using Sumproduct but failed..... need to learn more.

so I built on the post by BrianMH

Excel 2010
	A	B	C	D	E	F
1					At at	16/04/2014
2
3	Hired	Termed	Position		Position	Count
4	01/01/2001		Position A		Position A	1
5	15/01/2013	09/08/2013	Position A		Position B	2
6	17/07/2011	13/12/2013	Position A		Position C	2
7			Position B
8			Position B
9	19/08/2013		Position B
10	16/01/2013	12/04/2013	Position B
11	20/03/2014		Position B
12	22/05/2010	13/04/2014	Position B
13	23/07/2012		Position C
14	23/08/2012	15/07/2013	Position C
15	10/09/2009		Position C
16	10/09/2009	25/03/2014	Position C
17			Position C
Sheet1

Cell Formulas
Range	Formula
F4	{=SUM(--($C$4:$C$17=E4)*--(IF(ISBLANK($A$4:$A$17),$F$1+1,$A$4:$A$17)<=$F$1)*--(IF(ISBLANK($B$4:$B$17),$F$1,$B$4:$B$17)>=$F$1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dataAnalystB]

Thank you both for your input - I'm not strong in syntax, so I'll have to spend some time dissecting these to understand how they work.

The maddening thing about Excel is that it seems like there should be a very simple way to say "If Position equals Position A (B, C, etc.), then count the number of Hires within the Hired Column (or Terminations in Termed)."

I appreciate you taking the time to help, rest assured I'm going to try both suggestions!

Bryan

 

[Comfy]

Thank you both for your input - I'm not strong in syntax, so I'll have to spend some time dissecting these to understand how they work.

The maddening thing about Excel is that it seems like there should be a very simple way to say "If Position equals Position A (B, C, etc.), then count the number of Hires within the Hired Column (or Terminations in Termed)."

I appreciate you taking the time to help, rest assured I'm going to try both suggestions!

Bryan

There are simple ways but the question is very open-ended and can be interpreted multiple ways.