Count Distinct Unique values with nested IFs Formula

[Novice86]

I'm looking for a formula to count distinct values in column E (Order Numbers) if they are on a specific date in column M and if the orders are complete, or have zero left to build, meaning column R values are equal to zero.

I've used =SUMPRODUCT((Data!E2:E6000<>"")/COUNTIF(Data!E2:E6000,Data!E2:E6000&"")) to generate a list of all distinct order numbers but now cannot figure out how to incorporate a date and are complete.

Can anybody help?

 

[lrobbo314]

Here is a UDF that should do the trick. This code assumes that you have data from columns A to R. It can be adjusted if needed.

Function TOTALUNIQUE(r As Range, dt As Date) As DoubleDim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")
Dim AR() As Variant: AR = r.Value
Dim tmp As String


For i = LBound(AR) To UBound(AR)
    If AR(i, 13) = dt And AR(i, 18) = 0 Then
        tmp = AR(i, 5) & "|" & AR(i, 13)
        If Not AL.contains(tmp) Then AL.Add tmp
    End If
Next i


TOTALUNIQUE = AL.Count
End Function

and the formula would look like

=TOTALUNIQUE(A2:R7,DATE(2019,8,9))

Don't include the header row in the first argument.

I have PowerQuery and PivotTable solutions as well.