Countifs before a certain date

[andydarly]

Hi, all. I am looking to do a countifs formula, which is usually pretty straightforward. However, one of the criteria needs to specify unque values

Please note that I am on a work computer and it will not let me download xl2bb, so had to use an image. Sorry. I did try

So in the attached data I would like to count unique entries for all the people in a team with "_D" in it, are 1 FTE and have a start date of before the first of the next month, so July's data in this attachment would number 43. The number for August would be 44 and September would be 45 etc. The formula I am trying is below works but counts all entries, so is 20 higher for each month. Is there a simple way to do this unique value part? Thanks in advance.

My formula is =COUNTIFS($C:$C,"*_D*",$D:$D,1,$F:$F,"<01/08/2022")

 

[AhoyNC]

@Peter_SSs
Thank you. I do appreciate your knowledge on how to improve my formula.

 

[andydarly]

HI, all. I really appreciate your time spent on this but I cannot get either to work.

I have a countif that takes me most of the way there but instead of displaying it's result of 13 (for example) I would want that final result to have any duplicates removed so it recalculated to 7 (for example). I was hoping I guess for something along the lines of

=unique("result of my countifs formula","based on this criteria"). I just can't unravel your solutions into what I need in my small brain

 

[Peter_SSs]

have any duplicates removed

You have not specifically stated what you mean by "duplicates". I have assumed that you simply mean "duplicate" names that meet all the other criteria. For example, in my sample below all dates are before the K1 date so I have assumed that we are looking for unique (=distict as you mentioned in post 2) names that have "_D" in col C and are FTE=1. In that case my formula below is counting the green rows and not counting the amber duplicate of "Ruiters, Kagiso".

If that is not doing what you want then please clarify what result I should have and why.

If that is doing what you want but is not working for you, then perhaps you could provide a small set of sample data (with XL2BB so that we can copy and be sure to be using the same data as you) where the formula is not working and explain in relation to that sample data what the result should be and why.

andydarly.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	EMP_ID	EMP_SHORT_NAME	EMP_CLASS_1	FTE	Hours	START_DATE				Start Date Before	1/11/2022
2	NG0081	Ngcobo, Khanyisile	Service_D JHB2	1	75	18/09/2017				Count	6
3	NG0081	Ngcobo, Khanyisile	Service_N JHB9	1	75	1/04/2022
4	SA0576	Saba, Linda	Service_D JHB3	1	75	18/09/2017
5	SA0576	Saba, Linda	Service_N JHB6	1	75	1/04/2022
6	SE0236	Seqobane, Jennifer	Service_N JHB9	1	75	22/03/2022
7	SE0236	Seqobane, Jennifer	Service_N JHB5	1	75	1/07/2022
8	JI0334	Mokgosi, Xolile	Service_N JHB8	1	75	1/01/2022
9	JI0334	Mokgosi, Xolile	Service_N JHB4	1	75	1/08/2022
10	JI0334	Mokgosi, Xolile	Service_N JHB10	1	75	1/09/2022
11	KG0186	Kgasane, Lesego	Service_D JHB5	1	75	1/10/2020
12	MA0987	Mabetoa, Olgar	Service_N JHB1	1	75	2/12/2021
13	TS0257	Tsholetsasne, Boitumelo	Service_N JHB5	1	75	1/12/2021
14	ZU0768	Zulu, Faith	Service_N JHB10	1	75	18/09/2017
15	ZU0768	Zulu, Faith	Service_N JHB8	1	75	1/07/2022
16	ZU0768	Zulu, Faith	Service_N JHB7	1	75	22/08/2022
17	GO1158	Golding, Nicolette	Service_D JHB5	1	60	1/03/2021
18	MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB8	0.8	60	1/01/2022
19	MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB4	0.8	60	1/10/2022
20	BA0178	Banda, Ntando	Service_N JHB4	0.8	60	1/10/2020
21	BA0178	Banda, Ntando	Service_N JHB8	0.8	60	1/02/2022
22	MP5608	Mphahlele, Ouma Vicky	Service_N JHB5	1	75	1/12/2021
23	MP5608	Mphahlele, Ouma Vicky	Service_N JHB8	1	75	1/07/2022
24	MP5608	Mphahlele, Ouma Vicky	Service_D JHB5	1	75	1/09/2022
25	RU5561	Ruiters, Kagiso	Service_D JHB8	1	75	1/01/2021
26	RU5561	Ruiters, Kagiso	Service_D JHB4	1	75	1/10/2022
Sheet1

Cell Formulas
Range	Formula
K2	K2	=IFNA(ROWS(UNIQUE(FILTER(B2:B26,(ISNUMBER(SEARCH("_D",C2:C26)))*(D2:D26=1)*(F2:F26<K1),NA()))),0)

 

[andydarly]

You have not specifically stated what you mean by "duplicates". I have assumed that you simply mean "duplicate" names that meet all the other criteria. For example, in my sample below all dates are before the K1 date so I have assumed that we are looking for unique (=distict as you mentioned in post 2) names that have "_D" in col C and are FTE=1. In that case my formula below is counting the green rows and not counting the amber duplicate of "Ruiters, Kagiso".

If that is not doing what you want then please clarify what result I should have and why.

If that is doing what you want but is not working for you, then perhaps you could provide a small set of sample data (with XL2BB so that we can copy and be sure to be using the same data as you) where the formula is not working and explain in relation to that sample data what the result should be and why.

andydarly.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	EMP_ID	EMP_SHORT_NAME	EMP_CLASS_1	FTE	Hours	START_DATE				Start Date Before	1/11/2022
2	NG0081	Ngcobo, Khanyisile	Service_D JHB2	1	75	18/09/2017				Count	6
3	NG0081	Ngcobo, Khanyisile	Service_N JHB9	1	75	1/04/2022
4	SA0576	Saba, Linda	Service_D JHB3	1	75	18/09/2017
5	SA0576	Saba, Linda	Service_N JHB6	1	75	1/04/2022
6	SE0236	Seqobane, Jennifer	Service_N JHB9	1	75	22/03/2022
7	SE0236	Seqobane, Jennifer	Service_N JHB5	1	75	1/07/2022
8	JI0334	Mokgosi, Xolile	Service_N JHB8	1	75	1/01/2022
9	JI0334	Mokgosi, Xolile	Service_N JHB4	1	75	1/08/2022
10	JI0334	Mokgosi, Xolile	Service_N JHB10	1	75	1/09/2022
11	KG0186	Kgasane, Lesego	Service_D JHB5	1	75	1/10/2020
12	MA0987	Mabetoa, Olgar	Service_N JHB1	1	75	2/12/2021
13	TS0257	Tsholetsasne, Boitumelo	Service_N JHB5	1	75	1/12/2021
14	ZU0768	Zulu, Faith	Service_N JHB10	1	75	18/09/2017
15	ZU0768	Zulu, Faith	Service_N JHB8	1	75	1/07/2022
16	ZU0768	Zulu, Faith	Service_N JHB7	1	75	22/08/2022
17	GO1158	Golding, Nicolette	Service_D JHB5	1	60	1/03/2021
18	MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB8	0.8	60	1/01/2022
19	MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB4	0.8	60	1/10/2022
20	BA0178	Banda, Ntando	Service_N JHB4	0.8	60	1/10/2020
21	BA0178	Banda, Ntando	Service_N JHB8	0.8	60	1/02/2022
22	MP5608	Mphahlele, Ouma Vicky	Service_N JHB5	1	75	1/12/2021
23	MP5608	Mphahlele, Ouma Vicky	Service_N JHB8	1	75	1/07/2022
24	MP5608	Mphahlele, Ouma Vicky	Service_D JHB5	1	75	1/09/2022
25	RU5561	Ruiters, Kagiso	Service_D JHB8	1	75	1/01/2021
26	RU5561	Ruiters, Kagiso	Service_D JHB4	1	75	1/10/2022
Sheet1

Cell Formulas
Range	Formula
K2	K2	=IFNA(ROWS(UNIQUE(FILTER(B2:B26,(ISNUMBER(SEARCH("_D",C2:C26)))*(D2:D26=1)*(F2:F26<K1),NA()))),0)

Hi, I might not be explaining myself very well.

I'm sorry. I do struggle with things like that

 

[Peter_SSs]

So, are you all good now or do you still need help?

 

[andydarly]

Hi, all. I managed to have a play and get the correct numbers by combining COUNTA, UNIQUE and FILTER functions

Thank you all for your contributions

 

[andydarly]

Im not sure how to close the thread?

 

[Peter_SSs]

Im not sure how to close the thread?

We don't close threads in this forum. Even when a successful suggestion has been made and accepted, it is always possible that an even better solution might be posted later.

 

[andydarly]

We don't close threads in this forum. Even when a successful suggestion has been made and accepted, it is always possible that an even better solution might be posted later.