问题描述:

I am trying to calculate total number of orders based on a referral indicator. In my table I have 3 columns.

`order_id (a), sku (b), referral (c)`

9073765908 19333476 EMP_BC

9073765908 18717916 EMP_BC

9073741228 23224786 EMP_BC

9073729088 15870816 EMP_BC

9073238838 15927306 EMP_BC

9073216258 23210836 EMP_BC

9073216258 18709096 EMP_BC

9073216258 18705756 EMP_BC

9073169568 19333496 EMP_BC

9073169168 18714626 EMP_BC

9072921778 22723516 EMP_ES

9072921778 18708696 EMP_ES

9072921778 15933746 EMP_ES

9072910758 23223526 EMP_BC

9072910758 23184416 EMP_BC

9072910758 21081716 EMP_BC

9072910758 18704496 EMP_BC

9072895968 21082646 EMP_ES

9072856628 28685906 EMP_BC

9072856628 23795646 EMP_BC

9072856628 21447106 EMP_BC

9072856628 15936966 EMP_BC

Here is all the things I've tried thus far:

`=COUNTIFS(C:C, "=EMP_BC")`

However this returns total including the duplicates.

Also tried this:

`=SUMPRODUCT(1/COUNTIFS(A:A, A:A, C:C, "=EMP_BC"))`

This however, causes my excel to freeze... Im assuming this is due to the number of records I have... I have over 60K records.

[edit]

I am expected the following result:

`for EMP_BC: 8`

because the following order_id is unique and the referral is EMP_BC:

9073765908

9073741228

9073729088

9073238838

9073216258

9073169568

9072910758

9072856628

I currently have a tab specifically setup so that it can have data copied and pasted in a tab. Another tab with all the formulas will reference the data and display the results.

This one metric is giving me a lot of problems.

Any recommendations?

Here is an array formula that should accomplish what you want. To enter an array formula, hold down `ctrl-shift`

while hitting `enter`

. If you do this correctly, Excel will place braces `{...}`

around the formula.

```
=SUM(IFERROR(1/COUNTIFS(order_id,order_id,referral,"EMP_BC"),0))
```

This formula assumes the ranges comprise just the used range, and not the entire column.

It is possible that a User Defined Function written in VBA will execute much faster, even with limited range references.

To enter this User Defined Function (UDF), `alt-F11`

opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select `Insert/Module`

and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like `=UniqueOrdersByReferral(order_id,referral,"EMP_BC")`

in some cell.

```
Option Explicit
Option Compare Text 'Comment out this line to make case SENSITIVE
Function UniqueOrdersByReferral(rOrderID As Range, rReferral As Range, sReferralCode As String) As Long
Dim Col As Collection
Dim I As Long, vOID As Variant, vREF As Variant
vOID = rOrderID
vREF = rReferral
If UBound(vOID) <> UBound(vREF) Then
MsgBox "Order ID and Referral Ranges must be of same size"
Exit Function
End If
Set Col = New Collection
On Error Resume Next
For I = 1 To UBound(vOID)
If vREF(I, 1) = sReferralCode Then Col.Add vOID(I, 1), CStr(vOID(I, 1))
Next I
On Error GoTo 0
UniqueOrdersByReferral = Col.Count
End Function
```