Count Distinct Unique values with nested IFs Formula

Novice86

New Member
Joined
Jul 18, 2015
Messages
13
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?
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,048
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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
Code:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

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
Top