当前位置: 动力学知识库 > 问答 > 编程问答 >

Excel: How to count total number of unique values based on a value from a different column

问题描述:

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

分享给朋友:
您可能感兴趣的文章:
随机阅读: