Countifs without duplicate

[jyroxxor]

Help needed!
Data below shown John has 3 outlets in Jan. What would be the formula for me to put in C3 to get 3? If I change the manager to Smith, will the number change automatically as well? Thanks!

[TABLE="width: 488"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]Month[/TD]
[TD]Jan[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD] [/TD]
[TD]Manager[/TD]
[TD]John[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]No of Shop[/TD]
[TD]3[/TD]
[TD](Exclude duplicate)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Month[/TD]
[TD]Manager[/TD]
[TD]Shop[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]BBB[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]CCC[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]YYY[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]ZZZ[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]XXX[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]XXX[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]WWW[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]ZZZ[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]YYY[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]WWW[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Excel Workbook
	A	B	C	D
1		Month	Jan
2		Manager	John
3		No of Shop	3
4
5	Month	Manager	Shop	Staff
6	Jan	John	AAA	A
7	Jan	John	AAA	B
8	Jan	John	BBB	C
9	Jan	John	CCC	D
10	Jan	John	AAA	E
11	Jan	Smith	YYY	F
12	Jan	Smith	ZZZ	G
13	Jan	Smith	XXX	H
14	Jan	Smith	XXX	I
15	Jan	Smith	WWW	J
16	Jan	Smith	ZZZ	K
17	Jan	Smith	YYY	L
18	Jan	Smith	WWW	M
19	Feb	John	AAA	N
20	Feb	John	AAA	O
21	Feb	John	AAA	P
22	Feb	John	AAA	Q
23	Feb	John	AAA	R
24	Feb	John	AAA	S
Count

 

[MartyS]

Hi Peter,

Could I ask: is there a reason for the &"|"& instead of just &

e.g. C1 &"|"& C2 instead of just C1 & C2 ?

 

[Peter_SSs]

Hi Peter,

Could I ask: is there a reason for the &"|"& instead of just &

e.g. C1 &"|"& C2 instead of just C1 & C2 ?

It is just a safety measure. As a general example, look at columns A:B below. Without the delimiter (column C formula) all rows would appear to be the same. With the delimiter (column D formula) they are clearly all different.

Book1
	A	B	C	D
1	aa	bb	aabb	aa|bb
2	a	abb	aabb	a|abb
3		aabb	aabb	|aabb
Sheet5

Cell Formulas
Range	Formula
C1	=A1&B1
D1	=A1&"|"&B1

 

[MartyS]

Awesome - thank you!

 

[jyroxxor]

Welcome to the MrExcel board!

See if this does what you want. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}.

Count

	A	B	C	D
	Month	Jan
	Manager	John
	No of Shop
Month	Manager	Shop	Staff
Jan	John	AAA	A
Jan	John	AAA	B
Jan	John	BBB	C
Jan	John	CCC	D
Jan	John	AAA	E
Jan	Smith	YYY	F
Jan	Smith	ZZZ	G
Jan	Smith	XXX	H
Jan	Smith	XXX	I
Jan	Smith	WWW	J
Jan	Smith	ZZZ	K
Jan	Smith	YYY	L
Jan	Smith	WWW	M
Feb	John	AAA	N
Feb	John	AAA	O
Feb	John	AAA	P
Feb	John	AAA	Q
Feb	John	AAA	R
Feb	John	AAA	S

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:55px;"><col style="width:83px;"><col style="width:58px;"><col style="width:54px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]24[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula C3 {=COUNT(1/(MATCH(C1&"|"&C2&"|"&C6:C24,A6:A24&"|"&B6:B24&"|"&C6:C24,0)=ROW(C6:C24)-ROW(C6)+1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks Peter! It's the outcome that I want! Although I do not understand the logic behind... haha

 

[jyroxxor]

Hihi!

What would be the formula if there are 2 out of 3 shops are achieve more than 50% as shown below. Million thanks!

[TABLE="width: 374"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Month[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Manager[/TD]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No of Shop[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]No of Shop >50%[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD]Manager[/TD]
[TD]Shop[/TD]
[TD]Shop %[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]51%[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]51%[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]BBB[/TD]
[TD]30%[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]CCC[/TD]
[TD]60%[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]51%[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]YYY[/TD]
[TD]30%[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]ZZZ[/TD]
[TD]80%[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]XXX[/TD]
[TD]20%[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]XXX[/TD]
[TD]20%[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]WWW[/TD]
[TD]60%[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]ZZZ[/TD]
[TD]80%[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]YYY[/TD]
[TD]30%[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]Jan[/TD]
[TD]Smith[/TD]
[TD]WWW[/TD]
[TD]60%[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]90%[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]90%[/TD]
[TD]O[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]90%[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]BBB[/TD]
[TD]30%[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]CCC[/TD]
[TD]58%[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Feb[/TD]
[TD]John[/TD]
[TD]AAA[/TD]
[TD]90%[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly.
If you want to quote, quote small, relevant parts only, as I have done below.

What would be the formula if there are 2 out of 3 shops are achieve more than 50%

Try

Excel Workbook
	A	B	C	D
1		Month	Jan
2		Manager	Smith
3		No of Shop
4		No of Shop >50%	2
5
6	Month	Manager	Shop	Shop %
7	Jan	John	AAA	51%
8	Jan	John	AAA	51%
9	Jan	John	BBB	30%
10	Jan	John	CCC	60%
11	Jan	John	AAA	51%
12	Jan	Smith	YYY	30%
13	Jan	Smith	ZZZ	80%
14	Jan	Smith	XXX	20%
15	Jan	Smith	XXX	20%
16	Jan	Smith	WWW	60%
17	Jan	Smith	ZZZ	80%
18	Jan	Smith	YYY	30%
19	Jan	Smith	WWW	60%
20	Feb	John	AAA	90%
21	Feb	John	AAA	90%
22	Feb	John	AAA	90%
23	Feb	John	BBB	30%
24	Feb	John	CCC	58%
25	Feb	John	AAA	90%
Count 2

 

[jyroxxor]

Thanks Peter! You are the best!

 

[Peter_SSs]

Thanks Peter! You are the best!

You are welcome.

BTW, I have removed the long quote from your last post. Perhaps you missed the first part of my earlier post:

Best not to fully quote long posts as it makes the thread harder to read/navigate and just occupies storage space needlessly.
If you want to quote, quote small, relevant parts only ...