Countifs without duplicate

J

jyroxxor

New Member
Joined
Feb 26, 2018
Messages
11
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]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
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
ABCD
1MonthJan
2ManagerJohn
3No of Shop3
4
5MonthManagerShopStaff
6JanJohnAAAA
7JanJohnAAAB
8JanJohnBBBC
9JanJohnCCCD
10JanJohnAAAE
11JanSmithYYYF
12JanSmithZZZG
13JanSmithXXXH
14JanSmithXXXI
15JanSmithWWWJ
16JanSmithZZZK
17JanSmithYYYL
18JanSmithWWWM
19FebJohnAAAN
20FebJohnAAAO
21FebJohnAAAP
22FebJohnAAAQ
23FebJohnAAAR
24FebJohnAAAS
Count
 
Upvote 0
Hi Peter,

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

e.g. C1 &"|"& C2 instead of just C1 & C2 ?
 
Upvote 0
MartyS said:
Hi Peter,

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

e.g. C1 &"|"& C2 instead of just C1 & C2 ?
Click to expand...
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
ABCD
1aabbaabbaa|bb
2aabbaabba|abb
3aabbaabb|aabb
Sheet5
Cell Formulas
RangeFormula
C1=A1&B1
D1=A1&"|"&B1
 
Upvote 0
Peter_SSs said:
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

ABCD
MonthJan
ManagerJohn
No of Shop
MonthManagerShopStaff
JanJohnAAAA
JanJohnAAAB
JanJohnBBBC
JanJohnCCCD
JanJohnAAAE
JanSmithYYYF
JanSmithZZZG
JanSmithXXXH
JanSmithXXXI
JanSmithWWWJ
JanSmithZZZK
JanSmithYYYL
JanSmithWWWM
FebJohnAAAN
FebJohnAAAO
FebJohnAAAP
FebJohnAAAQ
FebJohnAAAR
FebJohnAAAS

<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
CellFormula
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
Click to expand...

Thanks Peter! It's the outcome that I want! Although I do not understand the logic behind... haha
 
Upvote 0
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]
 
Upvote 0
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.

jyroxxor said:
What would be the formula if there are 2 out of 3 shops are achieve more than 50%
Click to expand...
Try

Excel Workbook
ABCD
1MonthJan
2ManagerSmith
3No of Shop
4No of Shop >50%2
5
6MonthManagerShopShop %
7JanJohnAAA51%
8JanJohnAAA51%
9JanJohnBBB30%
10JanJohnCCC60%
11JanJohnAAA51%
12JanSmithYYY30%
13JanSmithZZZ80%
14JanSmithXXX20%
15JanSmithXXX20%
16JanSmithWWW60%
17JanSmithZZZ80%
18JanSmithYYY30%
19JanSmithWWW60%
20FebJohnAAA90%
21FebJohnAAA90%
22FebJohnAAA90%
23FebJohnBBB30%
24FebJohnCCC58%
25FebJohnAAA90%
Count 2
 
Upvote 0
jyroxxor said:
Thanks Peter! You are the best!
Click to expand...
You are welcome.

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

Peter_SSs said:
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 ...
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
1K
samide2001
S
M
  • Solved
Assigning name for Unique and Duplicated Values in a Column
Replies
5
Views
420
Peter_SSs
Peter_SSs
N
  • Question Question
Sum Hierarchy Data Using Flattened Hierarchy
Replies
1
Views
1K
neb255
N
Rob_010101
  • Solved
Complicated SUMIFS/COUNTIFS Formulas Required
Replies
5
Views
684
etaf
etaf
JTL9161
  • Question Question
HLookup?
Replies
0
Views
519
JTL9161
JTL9161

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top