Right Countif formula

[zinah]

Hi,

I need to count how many completed course by employee, if the Completed Date column has a date, then this course is completed but if it's a blank, then it's not completed. The catch with my current formula it counts the all employee ID even if the completed date is a blank, what is the right formula? what I need is to count Count of Completed per Unique User ID if the Completed_Date has a date only (not a blank or any figure or characters):
current formula =

=IFERROR(IF(A2="","",COUNTIF($C:$C,C2)),"")

Completed_Date	Course ID	Unique User ID	Count of Completed per Unique User ID
2/3/2021	2875263	SS200127281	1
9/16/2020	2823255	SS200087361	13
1/27/2021	761950	SS200124361	1
2/2/2021	761950	SS200125621	2
10/21/2020	2836026	SS200087361	13
9/16/2020	2839080	SS200087361	13
10/28/2020	696872	SS200087361	13
11/18/2020	808670	SS200087361	13
9/16/2020	2255004	SS200087361	13
11/4/2020	167027	SS200087361	13
2/3/2021	697728	SS200125621	2
2/19/2021	2975170	SS200124371	1
10/28/2020	778180	SS200087361	13
10/21/2020	784281	SS200087361	13
5/21/2020	779753	SS113045811	1
Migrated	397387	SS116265381	1
Migrated	533302	SS115411351	1
9/18/2020	2926134	SS112682271	1
Migrated	184146	SS115429311	1
9/26/2019	420303	SS114132571	1
7/14/2020	2822642	SS104639231	1
	2812537	SS200087361
	2818086	SS200087361
	743143	SS200087361
	753906	SS200087361

 

[jtakw]

Hi,

Try this:

Book3.xlsx
	A	B	C	D
2	2/3/2021	2875263	SS200127281	1
3	9/16/2020	2823255	SS200087361	4
4	10/21/2020		SS200087361	4
5	9/16/2020		SS200087361	4
6	Migrated		SS200087361
7	10/28/2020		SS200087361	4
8
Sheet815

Cell Formulas
Range	Formula
D2:D8	D2	=IF(ISNUMBER(A2),SUMPRODUCT((ISNUMBER(A$2:A$100)*(C$2:C$100=C2))),"")

 

[zinah]

Hi,

Try this:

Book3.xlsx
	A	B	C	D
2	2/3/2021	2875263	SS200127281	1
3	9/16/2020	2823255	SS200087361	4
4	10/21/2020		SS200087361	4
5	9/16/2020		SS200087361	4
6	Migrated		SS200087361
7	10/28/2020		SS200087361	4
8
Sheet815

Cell Formulas
Range	Formula
D2:D8	D2	=IF(ISNUMBER(A2),SUMPRODUCT((ISNUMBER(A$2:A$100)*(C$2:C$100=C2))),"")

Exactly what I wanted, thank you so much!

 

[jtakw]

You're welcome, thanks for the feedback.