Count, without repeating...

[evilacha]

HI guys, I'm getting addicted to this board, its very usefull. What i have its a sheet that has a column with clients and other column thats like a conditional, i want to count all the clients that have that conditional without counting a client twice... because the client list have clients that apper more than once...

Thanks for all your help guys.

 

[texasalynn]

what about this
=IF(COUNTIF($A$1:A1,A1)=1,1,0)

where column A has the client. So wherever the answer shows 1 is a unique count of that client.

 

[evilacha]

I tried that, but aint working... let me show you a small example:

[ B ][ L ]
[Client ][ Aladi ]
[ IND ] [ ]
[ACME ] [ Aladi ]
[ACME ] [ Aladi ]
[LASYS] [ Aladi ]
[ IND ] [ ]

So what i want to do its to count how many clients, meet the "Aladi" Requirement... in this example it would be 2: Acme and LASYS, because IND doesnt meet the requirement and it must count each client just once.

 

[evilacha]

tHX man i made a way through your formula, I starded with a filter, to show the Aladi clients, after that I applied your formula and made a sum of all the " 1 " and it worked.

 

[texasalynn]

or try this
=IF(AND(COUNTIF($A$1:A1,A1)=1,B1="Aladi"),1,0)

 

[Aladin Akyurek]

I tried that, but aint working... let me show you a small example:

[ B ][ L ]
[Client ][ Aladi ]
[ IND ] [ ]
[ACME ] [ Aladi ]
[ACME ] [ Aladi ]
[LASYS] [ Aladi ]
[ IND ] [ ]

So what i want to do its to count how many clients, meet the "Aladi" Requirement... in this example it would be 2: Acme and LASYS, because IND doesnt meet the requirement and it must count each client just once.

Book4
	B	C	D	E	F	G	H	I	J	K	L
2
3	Client										Aladi
4	IND
5	ACME										Aladi
6	ACME										Aladi
7	LASYS										Aladi
8	IND
9
10
11
12
13
14
15	2
16	2
17
Sheet1

B15:

=SUM(IF(FREQUENCY(IF($B$4:$B$8<>"",IF($L$4:$L$8="Aladi",MATCH($B$4:$B$8,$B$4:$B$8,0))),ROW(B4:B8)-ROW($B$4)+1),1))

which needs to be confirmed with control+shift+enter, not just with enter.

Or, alternatively, if you have installed the morefunc.xll add-in:

B16:

=COUNTDIFF(IF($L$4:$L$8="Aladi",IF($B$4:$B$8<>"",$B$4:$B$8)),FALSE,FALSE)

which also needs to be confirmed with control+shift+enter, not just with enter.