Sumproduct Datedif Multiple criterias

[Lmcb]

Hello everyone,

I would like to count with your help,

Im trying to do a tenure counting of persons per each month of the year, if the person is still active i do not insert date in the leave date column. I would like to use sumproduct if possible, and ignore the black leave date cells from the active sheet.

So for each month as per example January 2021, how many persons were working with less than 3 months, with more than 6 months and less than a year, and with more than 18 months, including persons who left and the actual active persons.

Can you someone send me suggestions?

Thanks

 

[Eric W]

Welcome to the MrExcel forum!

Try:

Book4 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1	Hire Date	Leave Date		Counter	00-03	03-06	06-12	12-18	18-99999
2	6-Nov-20	2-Jul-21
3	8-Jan-21	3-Jul-21		Jan-21	36	1	0	0	0
4	14-May-21	3-Jul-21		Feb-21	15	27	0	0	0
5	8-Jan-21	7-Jul-21		Mar-21	5	37	0	0	0
6	16-Oct-20	9-Jul-21		Apr-21	5	36	1	0	0
7	29-Jan-21	12-Jul-21		May-21	1	15	27	0	0
8	16-Oct-20	15-Jul-21		Jun-21	7	5	37	0	0
9	6-Nov-20	16-Jul-21		Jul-21	8	5	36	0	0
10	14-May-21	17-Jul-21		Aug-21	4	1	32	0	0
11	14-May-21	22-Jul-21		Sep-21	1	2	26	0	0
12	16-Oct-20	22-Jul-21		Oct-21	0	3	24	1	0
13	6-Nov-20	1-Jul-21		Nov-21	0	2	5	23	0
14	27-Nov-20	2-Jul-21		Dec-21	0	1	2	25	0
15	14-May-21	2-Aug-21
16	14-May-21	9-Aug-21
17	27-Nov-20	16-Aug-21
18	6-Nov-20	20-Aug-21
19	8-Jan-21	23-Aug-21
20	11-Jan-21	24-Aug-21
21	16-Oct-20	24-Aug-21
22	11-Sep-20
23	16-Oct-20
24	16-Oct-20
25	21-May-21
26	9-Jun-21
27	16-Oct-20
28	1-May-21
29	16-Oct-20
30	16-Oct-20
31	6-Nov-20
32	27-Nov-20
33	27-Nov-20
34	6-Nov-20
35	16-Oct-20
36	16-Oct-20
37	16-Oct-20
38	16-Oct-20
39	16-Oct-20
40	16-Oct-20
41	16-Oct-20	20-Sep-21
42	16-Oct-20
43	16-Oct-20
44	16-Oct-20
45	16-Oct-20
46	16-Oct-20
47	16-Oct-20
48	16-Oct-20	1-Sep-21
49	16-Oct-20
50	16-Oct-20
51	23-Oct-20
Sheet10

Cell Formulas
Range	Formula
E3:I14	E3	=SUMPRODUCT(--(($B$2:$B$51>$D3)+($B$2:$B$51="")),--($D3-$A$2:$A$51>=LEFT(E$1,2)*30),--($D3-$A$2:$A$51<=MID(E$1,4,5)*30))

Incidentally, this forum has a tool called XL2BB, which I just used to show my results. It makes it easier to copy data to test with. You probably would have had a quicker response if you'd used it. Check out the XL2BB link in my signature or the response box. It's easy to download, install, and use.