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!

	A	B	C	D
1		Month	Jan
2		Manager	John
3		No of Shop	3	(Exclude duplicate)
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

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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
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

<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>
</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!

	Month	Jan
	Manager	John
	No of Shop	3
	No of Shop >50%	2
Month	Manager	Shop	Shop %	Staff
Jan	John	AAA	51%	A
Jan	John	AAA	51%	B
Jan	John	BBB	30%	C
Jan	John	CCC	60%	D
Jan	John	AAA	51%	E
Jan	Smith	YYY	30%	F
Jan	Smith	ZZZ	80%	G
Jan	Smith	XXX	20%	H
Jan	Smith	XXX	20%	I
Jan	Smith	WWW	60%	J
Jan	Smith	ZZZ	80%	K
Jan	Smith	YYY	30%	L
Jan	Smith	WWW	60%	M
Feb	John	AAA	90%	N
Feb	John	AAA	90%	O
Feb	John	AAA	90%	P
Feb	John	BBB	30%	Q
Feb	John	CCC	58%	R
Feb	John	AAA	90%	S

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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 ...