# Count of unique occurrences with multiple criteria/exceptions

#### sdk1013

##### New Member
I need help finding the count of unique encounters for data similar to the example below. A unique encounter = unique ID at a unique location receiving a unique service on a unique date by a unique provider. I need to be able to filter and run unique encounter counts based on those variables, i.e. unique encounters at location VA vs CA, for service a vs b vs c, on [date], and by provider x vs y.

Thanks so much for any guidance!

 ID LOCATION SERVICE SERVICE DATE PROVIDER 1​ VA a 12/1/2021​ x 1​ VA a 12/1/2021​ x 1​ VA a 12/1/2021​ y 1​ VA b 12/1/2021​ x 1​ VA a 12/2/2021​ x 1​ VA b 12/2/2021​ y 1​ VA c 12/1/2021​ x 1​ VA c 12/3/2021​ x 1​ VA c 12/2/2021​ y 2​ CA a 12/1/2021​ x 2​ CA a 12/1/2001​ y 2​ CA c 12/1/2021​ x 2​ CA c 12/1/2021​ x 2​ CA b 12/2/2021​ x 2​ CA b 12/2/2021​ y 2​ CA a 12/2/2021​ x 2​ CA b 12/2/2021​ x

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
this subroutine will give the count of the uniques in all 5 columns , if you just want the first 4 just change the j loop to 4, ditto 3, and 2. If you want to miss one out in the middle just put and if statement in to skip that loop
VBA Code:
``````Sub Countoccurence()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ary = Worksheets("Sheet1").Range(Cells(1, 1), Cells(lastrow, 5))
Dim Dic As Object
Set Dic = CreateObject("Scripting.dictionary")

For i = 1 To UBound(ary, 1)
' concatenate 5 columns
tt = ""
For j = 1 To 5
tt = tt & ary(i, j)
Next j
Dic(tt) = i
Next i
MsgBox ("Number of unique values is " & Dic.Count)

End Sub``````

Welcome to the MrExcel forum!

Try:

Book2
ABCDEFGHIJKLM
1IDLOCATIONSERVICESERVICE DATEPROVIDERIDLocationServiceService DateProviderUnique count
21VAa12/1/2021x*VA*12/2/2021*3
31VAa12/1/2021x
41VAa12/1/2021y
51VAb12/1/2021x
61VAa12/2/2021x
71VAb12/2/2021y
81VAc12/1/2021x
91VAc12/3/2021x
101VAc12/2/2021y
112CAa12/1/2021x
122CAa12/1/2021y
132CAc12/1/2021x
142CAc12/1/2021x
152CAb12/2/2021x
162CAb12/2/2021y
172CAa12/2/2021x
182CAb12/2/2021x
Sheet4
Cell Formulas
RangeFormula
M2M2=ROWS(UNIQUE(FILTER(A2:E18,((A2:A18=G2)+(G2="*"))*((B2:B18=H2)+(H2="*"))*((C2:C18=I2)+(I2="*"))*((D2:D18=J2)+(J2="*"))*((E2:E18=K2)+(K2="*")))))

In the G2:K2 cells, put an asterisk ("*") in the cell if you don't want to restrict that column. Otherwise, put the value you want to see.

Welcome to the MrExcel forum!

Try:

Book2
ABCDEFGHIJKLM
1IDLOCATIONSERVICESERVICE DATEPROVIDERIDLocationServiceService DateProviderUnique count
21VAa12/1/2021x*VA*12/2/2021*3
31VAa12/1/2021x
41VAa12/1/2021y
51VAb12/1/2021x
61VAa12/2/2021x
71VAb12/2/2021y
81VAc12/1/2021x
91VAc12/3/2021x
101VAc12/2/2021y
112CAa12/1/2021x
122CAa12/1/2021y
132CAc12/1/2021x
142CAc12/1/2021x
152CAb12/2/2021x
162CAb12/2/2021y
172CAa12/2/2021x
182CAb12/2/2021x
Sheet4
Cell Formulas
RangeFormula
M2M2=ROWS(UNIQUE(FILTER(A2:E18,((A2:A18=G2)+(G2="*"))*((B2:B18=H2)+(H2="*"))*((C2:C18=I2)+(I2="*"))*((D2:D18=J2)+(J2="*"))*((E2:E18=K2)+(K2="*")))))

In the G2:K2 cells, put an asterisk ("*") in the cell if you don't want to restrict that column. Otherwise, put the value you want to see.
This is great, thanks so much!
As a follow-up question to take this to the next level, how would you go about making that output table include all the options for location, service, date, and provider? Is that possible? I'm imagining a filter drop down for those columns so that I can select whatever exclusions readily. For example, a filter drop down for location having *, VA, and CA, and one for service having *, a, b, and c.

Try this:

Book1
ABCDEFGHIJKLM
1IDLOCATIONSERVICESERVICE DATEPROVIDERIDLocationServiceService DateProviderUnique count
21VAa12/1/2021x*VA*12/2/2021*3
31VAa12/1/2021x
41VAa12/1/2021y
51VAb12/1/2021x
61VAa12/2/2021x
71VAb12/2/2021y
81VAc12/1/2021xIDLocationServiceService DateProvider
91VAc12/3/2021x1VAa44531x
101VAc12/2/2021y2CAb12/2/2021y
112CAa12/1/2021x**c12/3/2021*
122CAa12/1/2021y**
132CAc12/1/2021x
142CAc12/1/2021x
152CAb12/2/2021x
162CAb12/2/2021y
172CAa12/2/2021x
182CAb12/2/2021x
19
Sheet7
Cell Formulas
RangeFormula
M2M2=ROWS(UNIQUE(FILTER(A2:E18,((A2:A18=G2)+(G2="*"))*((B2:B18=H2)+(H2="*"))*((C2:C18=I2)+(I2="*"))*((D2:D18=J2)+(J2="*"))*((E2:E18=K2)+(K2="*")))))
K9:K11,I9:J12,G9:H11G9=IFERROR(INDEX(UNIQUE(A2:A18),SEQUENCE(ROWS(UNIQUE(A2:A18))+1)),"*")
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
G2:K2List=G9#

The formula to get a unique list of values is just =UNIQUE(A2:A18) but I had to make it a bit more complicated to get the * on the list. Then once you have the unique lists (and you can put them anywhere, even on another sheet), you can use them in a Data Validation drop-down list.

Replies
9
Views
395
Replies
5
Views
394
Replies
10
Views
97
Replies
7
Views
206
Replies
6
Views
178

1,207,199
Messages
6,077,023
Members
446,251
Latest member
dpf220

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

### Which adblocker are you using?

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

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