Count unique values with 2 conditions

E

esmarques

New Member
Joined
Oct 27, 2020
Messages
21
Office Version
  1. 365
Platform
  1. MacOS
Hello,
I am wondering if there is a way of counting unique values (per name on column F) in the table attached without applying any further filters as I will need it to stay at it is for other calculations.
So I would need to now for example, how many unique artitsts are from Bolton, from Bury etc... how many unique staff from Bolton, Bury, etc...

At the moment my formula is showing how many times each district appears per artist, staff etc but not considering if its the same name, so if same person signed in twice, it will count twice but I want it to count only once as its the same person.

Could anybody help?

Thank you


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJ
1in out referrer date time name postcode email phone reason artist staff visitor volunteer participant tutor trades deliveryaudienceatendeeDistrictMonthBoltonBuryManchesterOldhamRochdaleSalfordStockportTamesideTraffordWiganGM Non-Disttit SpecificTOTAL
2sign in !81.149.159.1518/1/2311:49:33matt bennettsk15 3lhTRUETamesideAug-23 artist100000000001
3sign in !81.149.159.1518/1/2310:21:31Becky Taylor-ChristianSK15 3BUTRUETamesideAug-23 staff005000040009
4sign in !81.149.159.1518/1/2310:09:46amelia baylissBL5 3SBTRUEBoltonAug-23 visitor000000000000
5sign in !81.149.159.1519/3/239:28:24Holly Prestol50dwTRUETamesideSep-23 volunteer000000020002
6sign in !81.149.159.1519/3/239:13:28Eraldo MarquesM125LRTRUEManchesterSep-23 participant100000000001
7sign in !81.149.159.15110/15/239:13:18Freya Bennett-NielsenSK15 3LHTRUETamesideOct-23 tutor000000000000
8sign in !81.149.159.15110/15/2318:49:51Alex GouldingBL5 3SBTRUEBoltonOct-23 trades000000000000
9sign in !81.149.159.15110/15/2318:47:45jon mamboOL5 0QRTRUETamesideOct-23 delivery000000000000
10sign in !81.149.159.15110/15/2310:31:10leon patelol50spTRUETamesideOct-23audience000000000000
11sign in !81.149.159.15110/15/2310:00:30Ciara BartlettM4 4EETRUEManchesterOct-23atendee000000000000
12sign in !81.149.159.15111/3/239:13:28Eraldo MarquesM125LRTRUEManchesterNov-23TOTAL2050000600013
13sign in !81.149.159.15111/5/239:13:28Eraldo MarquesM125LRTRUEManchesterNov-23
14sign in !81.149.159.15112/2/239:13:28Eraldo MarquesM125LRTRUEManchesterDec-23
15
Sheet1
Cell Formulas
RangeFormula
Y2:AH2Y2=COUNTIFS($K:$K,"TRUE",$U:$U,Y$1)
AI2AI2=COUNTIFS($K:$K,"TRUE",$U:$U,AI$40)
AJ2:AJ11AJ2=SUM(Y2:AI2)
Y3:AH3Y3=COUNTIFS($L:$L,"TRUE",$U:$U,Y$1)
AI3AI3=COUNTIFS($L:$L,"TRUE",$U:$U,AI$40)
Y4:AH4Y4=COUNTIFS($M:$M,"TRUE",$U:$U,Y$1)
AI4AI4=COUNTIFS($M:$M,"TRUE",$U:$U,AI$40)
Y5:AH5Y5=COUNTIFS($N:$N,"TRUE",$U:$U,Y$1)
AI5AI5=COUNTIFS($N:$N,"TRUE",$U:$U,AI$40)
Y6:AH6Y6=COUNTIFS($O:$O,"TRUE",$U:$U,Y$1)
AI6AI6=COUNTIFS($O:$O,"TRUE",$U:$U,AI$40)
Y7:AH7Y7=COUNTIFS($P:$P,"TRUE",$U:$U,Y$1)
AI7AI7=COUNTIFS($P:$P,"TRUE",$U:$U,AI$40)
Y8:AH8Y8=COUNTIFS($Q:$Q,"TRUE",$U:$U,Y$1)
AI8AI8=COUNTIFS($Q:$Q,"TRUE",$U:$U,AI$40)
Y9:AH9Y9=COUNTIFS($R:$R,"TRUE",$U:$U,Y$1)
AI9AI9=COUNTIFS($R:$R,"TRUE",$U:$U,AI$40)
Y10:AH10Y10=COUNTIFS($S:$S,"TRUE",$U:$U,Y$1)
AI10AI10=COUNTIFS($S:$S,"TRUE",$U:$U,AI$40)
Y11:AH11Y11=COUNTIFS($T:$T,"TRUE",$U:$U,Y$1)
AI11AI11=COUNTIFS($T:$T,"TRUE",$U:$U,AI$40)
Y12:AJ12Y12=SUM(Y2:Y9)
V2:V14V2=TEXT(D2,"mmm-YY")
 
Sort by date Sort by votes
Hello,
Sorry to bother again, and please let me know if its better to start a new thread but its just one last column on this same doc that I am struggling to find the formula.
This would be to count those unique names from other districs other than the ones given or not found (column AJ). So in the table if you see on line 10, there is a tutor from Chesire East, but its not in the required specific Districts, its not counting him.
Please let me know if you need any further queries or if its better to start a new thread.

Thank you very much...

Book5
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
1in out referrer date time name postcode email phone reason artist staff visitor volunteer participant tutor trades deliveryaudienceatendeeDistrictMonthBoltonBuryManchesterOldhamRochdaleSalfordStockportTamesideTraffordWiganNot FoundUsers Outside GMTOTAL
2out81.149.159.1519/30/2316:11:51JonTRUETamesideSep-23 artist000000010001
3out81.149.159.1519/30/2312:54:15MarthaTRUETamesideSep-23 staff001000010002
4in81.149.159.1519/30/2310:21:49JayneTRUETamesideSep-23 visitor000000020002
5out81.149.159.1519/29/2317:43:04AmeliaTRUENot foundSep-23 volunteer000000000000
6out81.149.159.1519/29/2312:39:23JudeTRUEKirkleesSep-23 participant000000000000
7out81.149.159.1519/29/2311:52:27MelanieTamesideSep-23 tutor000000010001
8in81.149.159.1519/29/2311:18:10DanTRUENot foundSep-23 trades000000000000
9in81.149.159.1519/29/2310:12:31EraldoTRUEManchesterSep-23 delivery000000000000
10out81.149.159.1519/20/2320:57:41JackTRUECheshire EastSep-23audience000000000000
11out81.149.159.1519/19/2321:45:03RichardTRUETamesideSep-23atendee000000000000
12out81.149.159.1519/19/2321:44:48SianTRUETamesideSep-23No Data000000010001
13sign in !03/07/2319:00JackTRUECheshire EastJul-23TOTAL0010000500006
14sign in !03/07/2319:00JackTRUECheshire EastJul-23
Sheet1
Cell Formulas
RangeFormula
Y2:AH11Y2=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$1000,($U$2:$U$1000=Y$1)*(INDEX($K$2:$T$1000,,XMATCH($X2,$K$1:$T$1)))))),0)
AI2:AI11AI2=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$1000,($U$2:$U$1000=AI$40)*(INDEX($K$2:$T$1000,,XMATCH($X2,$K$1:$T$1)))))),0)
Y12:AI12Y12=SUM(--(MMULT(($U$2:$U$1000=Y1)*($K$2:$T$1000=""),SEQUENCE(10,,,0))=10))
AK2:AK12AK2=SUM(Y2:AJ2)
Y13:AK13Y13=SUM(Y2:Y9)
V2:V14V2=TEXT(D2,"mmm-YY")
 
Upvote 0
Fluff said:
How about
Excel Formula: 
=IFERROR(ROWS(UNIQUE(FILTER($F$2:$F$1000,(ISNA(XMATCH($U$2:$U$1000,$Y$1:$AI$1)))*(INDEX($K$2:$T$1000,,XMATCH($X2,$K$1:$T$1)))))),0)
Click to expand...
Genius! Thank you very very much !
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Formula not working
Replies
1
Views
98
RoryA
RoryA
K
  • Question
conditional formatting question
Replies
2
Views
233
kerm007
K
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
222
Alex Blakenburg
A
K
  • Solved
Update to existing Move data macro
2
Replies
11
Views
425
Alex Blakenburg
A
P
  • Question
Having trouble with forumla to use Countifs for only specific names listed in a column
Replies
1
Views
457
Paradigmgaming911
P

Forum statistics

Threads
1,216,124
Messages
6,128,990
Members
449,480
Latest member
yesitisasport

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