Unique count

[vmjan02]

I have this table what is required if the column Apr'23 cell is blank then no action but if has value then it to do a unique client count for column Actual Client Name output in column A.

Is their is a way as i tried a to but no desired result.

C count - Desired (Actual Client Name as Ref)	Actual Client Name	Apr'23
1​	Bank of Maharashtra	0
2​	Bank of Maharashtra	1
1​	DIPG (Goa)	1
1​	Nudge AWC FOUNDATION	1
2​	Nudge AWC FOUNDATION	1
1​	Adani Estate Management	1
1​	ECGC	1
1​	Sanofi	1
1​	Symbiosis Inst of Law Management	1
1​	APV- Citadel	1
1​	Directorate of information (G-20/Gujarat)	1
	Nucleus Office Parks
3​	Bank of Maharashtra	2
1​	Dezerv	2
2​	Dezerv	2
	ABC MF
	Adani Estate Management
	Aegon Life Insurance
	AMFI

 

[nemmi69]

Assuming client names are in B

=IF(C2=0,1,COUNTIF($B$2:$B$20,B2))

 

[JvdV]

This seems more like a running count than a unique count? If so, then try:

Book1
	A	B	C
1	C count - Desired (Actual Client Name as Ref)	Actual Client Name	Apr'23
2	1	Bank of Maharashtra	0
3	2	Bank of Maharashtra	1
4	1	DIPG (Goa)	1
5	1	Nudge AWC FOUNDATION	1
6	2	Nudge AWC FOUNDATION	1
7	1	Adani Estate Management	1
8	1	ECGC	1
9	1	Sanofi	1
10	1	Symbiosis Inst of Law Management	1
11	1	APV- Citadel	1
12	1	Directorate of information (G-20/Gujarat)	1
13		Nucleus Office Parks
14	3	Bank of Maharashtra	2
15	1	Dezerv	2
16	2	Dezerv	2
17		ABC MF
18		Adani Estate Management
19		Aegon Life Insurance
20		AMFI
Sheet1

Cell Formulas
Range	Formula
A2:A20	A2	=IF(C2<>"",COUNTIF(B$2:B2,B2),"")

 

[nemmi69]

Try in A21

=SUMPRODUCT(1/COUNTIFS(B2:B20,B2:B20,C2:C20,"<>"""))

 

[vmjan02]

=IF(C2<>"",COUNTIF(B$2:B2,B2),"")

here is not working

Uniq CC RO IN	Actual Cleint Name	Apr'23
	CG Govt
	CG Govt
	CG Govt
5 (this should be 1)	CG Govt	4
5 (this should be 2)	CG Govt	4

 

[vmjan02]

=SUMPRODUCT(1/COUNTIFS(B2:B20,B2:B20,C2:C20,"<>"""))

Its giving me an error as #value

 

[Fluff]

How about

=IF(C2<>"",COUNTIF(B$2:B2,B2,C$2:C2,"<>"),"")

 

[vmjan02]

=IF(C2<>"",COUNTIF(B$2:B2,B2,C$2:C2,"<>"),"")

Is it countif or countifs, as countif will not work, can you please recheck

 

[Fluff]

You're right, it should be countifs

 

[vmjan02]

You're right, it should be countifs

I tried countifs as well it is giving zero, not giving count as 1, 2, 3