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")

 

[andydarly]

*******Correction******* I realise I do not mean "unique" values. I mean distinct values - apologies

 

[bebo021999]

To count for July2022:

=COUNTIFS($C:$C,"*_D*",$D:$D,1,$F:$F,">=01/07/2022",$F:$F,"<01/08/2022")

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

it will not let me download xl2bb, so had to use an image.

In that case consider just a direct copy/paste from your worksheet and tell us what the range is. That way we can at least copy the data for testing.
For example, this is A1:F2

EMP_ID		EMP_CLASS_1	FTE	Hours	START DATE
NGoo81		Service_D JHB2	1​	75​	18/09/2017​

 

[andydarly]

To count for July2022:

=COUNTIFS($C:$C,"*_D*",$D:$D,1,$F:$F,">=01/07/2022",$F:$F,"<01/08/2022")

Hi, bebo21999 Thank you for your reply. Sadly this will not work as it will only give me the people in July. I need all before before August. Your version would also give me the duplicates too

 

[andydarly]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)


In that case consider just a direct copy/paste from your worksheet and tell us what the range is. That way we can at least copy the data for testing.
For example, this is A1:F2

EMP_ID		EMP_CLASS_1	FTE	Hours	START DATE
NGoo81		Service_D JHB2	1​	75​	18/09/2017​

Hi, Peter. I did not realise I could do that. Thank you.

Looking athe below if we go with the same dates - so all the individuals before 01/08/2022 in the Start Date...

My countifs formula will give me 19 rows but I need to know how many different people this is (the answer in this data set would be 13). I know I could do this in Power Query by removing duplicates but that would cause something else to break.

I'm hoping I can put the countifs formula inside some sort of distinct formula or vice versa or something?

EMP_ID	EMP_SHORT_NAME	EMP_CLASS_1	FTE	Hours	START_DATE
NG0081	Ngcobo, Khanyisile	Service_D JHB2	1	75	18/09/17
NG0081	Ngcobo, Khanyisile	Service_N JHB9	1	75	01/04/22
SA0576	Saba, Linda	Service_D JHB3	1	75	18/09/17
SA0576	Saba, Linda	Service_N JHB6	1	75	01/04/22
SE0236	Seqobane, Jennifer	Service_N JHB9	1	75	22/03/22
SE0236	Seqobane, Jennifer	Service_N JHB5	1	75	01/07/22
JI0334	Mokgosi, Xolile	Service_N JHB8	1	75	01/01/22
JI0334	Mokgosi, Xolile	Service_N JHB4	1	75	01/08/22
JI0334	Mokgosi, Xolile	Service_N JHB10	1	75	01/09/22
KG0186	Kgasane, Lesego	Service_D JHB5	1	75	01/10/20
MA0987	Mabetoa, Olgar	Service_N JHB1	1	75	02/12/21
TS0257	Tsholetsasne, Boitumelo	Service_N JHB5	1	75	01/12/21
ZU0768	Zulu, Faith	Service_N JHB10	1	75	18/09/17
ZU0768	Zulu, Faith	Service_N JHB8	1	75	01/07/22
ZU0768	Zulu, Faith	Service_N JHB7	1	75	22/08/22
GO1158	Golding, Nicolette	Service_D JHB5	1	60	01/03/21
MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB8	0.8	60	01/01/22
MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB4	0.8	60	01/10/22
BA0178	Banda, Ntando	Service_N JHB4	0.8	60	01/10/20
BA0178	Banda, Ntando	Service_N JHB8	0.8	60	01/02/22
MP5608	Mphahlele, Ouma Vicky	Service_N JHB5	1	75	01/12/21
MP5608	Mphahlele, Ouma Vicky	Service_N JHB8	1	75	01/07/22
MP5608	Mphahlele, Ouma Vicky	Service_D JHB5	1	75	01/09/22
RU5561	Ruiters, Kagiso	Service_D JHB8	1	75	01/01/21
RU5561	Ruiters, Kagiso	Service_D JHB4	1	75	01/10/22

 

[Peter_SSs]

I'm hoping I can put the countifs formula inside some sort of distinct formula or vice versa or something?

Possibly, but to know what is available in your version of Excel we need you to act on my previous suggestion.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[andydarly]

Possibly, but to know what is available in your version of Excel we need you to act on my previous suggestion.

Apologies. I forgot that bit. I am specifically using Microsoft 365 MSO (Version 2202 Build 16.0.14931.20764) 64-bit

 

[AhoyNC]

Try:

Book1
	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	8/1/2022
2	NG0081	Ngcobo, Khanyisile	Service_D JHB2	1	75	9/18/2017				Count	5
3	NG0081	Ngcobo, Khanyisile	Service_N JHB9	1	75	4/1/2022
4	SA0576	Saba, Linda	Service_D JHB3	1	75	9/18/2017
5	SA0576	Saba, Linda	Service_N JHB6	1	75	4/1/2022
6	SE0236	Seqobane, Jennifer	Service_N JHB9	1	75	3/22/2022
7	SE0236	Seqobane, Jennifer	Service_N JHB5	1	75	7/1/2022
8	JI0334	Mokgosi, Xolile	Service_N JHB8	1	75	1/1/2022
9	JI0334	Mokgosi, Xolile	Service_N JHB4	1	75	8/1/2022
10	JI0334	Mokgosi, Xolile	Service_N JHB10	1	75	9/1/2022
11	KG0186	Kgasane, Lesego	Service_D JHB5	1	75	10/1/2020
12	MA0987	Mabetoa, Olgar	Service_N JHB1	1	75	12/2/2021
13	TS0257	Tsholetsasne, Boitumelo	Service_N JHB5	1	75	12/1/2021
14	ZU0768	Zulu, Faith	Service_N JHB10	1	75	9/18/2017
15	ZU0768	Zulu, Faith	Service_N JHB8	1	75	7/1/2022
16	ZU0768	Zulu, Faith	Service_N JHB7	1	75	8/22/2022
17	GO1158	Golding, Nicolette	Service_D JHB5	1	60	3/1/2021
18	MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB8	0.8	60	1/1/2022
19	MA0293	Mazibuko, Thuli-Nonkonzo	Service_D JHB4	0.8	60	10/1/2022
20	BA0178	Banda, Ntando	Service_N JHB4	0.8	60	10/1/2020
21	BA0178	Banda, Ntando	Service_N JHB8	0.8	60	2/1/2022
22	MP5608	Mphahlele, Ouma Vicky	Service_N JHB5	1	75	12/1/2021
23	MP5608	Mphahlele, Ouma Vicky	Service_N JHB8	1	75	7/1/2022
24	MP5608	Mphahlele, Ouma Vicky	Service_D JHB5	1	75	9/1/2022
25	RU5561	Ruiters, Kagiso	Service_D JHB8	1	75	1/1/2021
26	RU5561	Ruiters, Kagiso	Service_D JHB4	1	75	10/1/2022
Sheet3

Cell Formulas
Range	Formula
K2	K2	=ROWS(UNIQUE(FILTER($B$2:$B$26,(ISNUMBER(SEARCH("_D",$C$2:$C$26)))*($D$2:$D$26=1)*($F$2:$F$26<=$K$1),"")))

 

[Peter_SSs]

@andydarly
Thanks for updating your version details.

@AhoyNC
Some 'tweaking' comments about your solution.

• I do not know if it would be possible with the OP's data but if no rows meet the criteria (you seemed to have tried to allow for this with the "" for if empty argument in the filter function) your formula would still return 1 since the "" would occupy a row.
  
  

• a start date of before the first of the next month

  That would imply that your K1 needs to be the last day of a month, or perhaps better to use "<" instead of "<=" when comparing column F to K1
  
  
• Makes no difference to the results but as the question is asked there should be no need for any $ signs in the formula.

So, my tweaks to account for the above would be

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