Distinct Count if the value of another field is > 0

[Edwardryez]

Trying to modify the formula below:
{= SUM(IF(C2:C233<>"",1/COUNTIF(C2:C233,C2:C233)))}
Based on the Values in the column:
H2:H233<>""

How can i put this together?

 

[Edwardryez]

Thanks for reassurance.

Thank you Gents for both versions and for the additional explonation.

 

[RickXL]

No problem and thanks for the feedback.

We have both got something out of this question.
I got a free lesson from an expert on formula performance so I have some homework to do now!

Regards,

 

[RickXL]

For completeness I thought I would add the results from my homework.

My original formula that was based on CountIfs ran quickly enough on my PC (Intel Q6600 CPU) for small numbers of rows. By small numbers I mean up to about 500.
The much better formula based on Frequency was about five to six times quicker and it maintained this ratio as the rows increased.

I thought I would try a macro solution based on the Dictionary object. Surprisingly (to me at least), this proved to be much faster than either. Even with 100,000 rows it takes less than a second.

So I ran some tests and publish the results here:

Rows	CountIf	Frequency	Macro
1000	0.16361	0.03346	0.005
2000	0.62686	0.10197	0.009
5000	3.63271	0.67623	0.020
10000	14.8772	3.06531	0.039
20000	58.70127	10.86684	0.080
30000	138.2719	25.15883	0.206
40000		42.11806	0.211
50000		81.78617	0.285
75000		222.51752	0.480
100000		438.64851	0.847

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

Sub DistinctCountWithRestrictions()

    Dim ws As Worksheet
    Dim arrListC As Variant
    Dim arrListH
    Dim dicList As Dictionary
    Dim lr As Long

    Set ws = ThisWorkbook.Worksheets("Sheet3")
    Set dicList = New Dictionary

    With ws
        lr = .Cells(.Rows.Count, "C").End(xlUp).Row
        arrListC = .Range("C2:C" & lr)
        arrListH = .Range("H2:H" & lr)
        For i = 1 To UBound(arrListC)
            If Not dicList.Exists(arrListC(i, 1)) Then
                If arrListC(i, 1) <> "" And arrListH(i, 1) <> "" Then
                    dicList.Add arrListC(i, 1), 1
                End If
            End If
        Next
        .Cells(1, "C").Value = dicList.Count
      End With

End Sub

 

[RickXL]

In fact, by removing the "exists" check it runs even quicker (0.6 seconds for 100,000 rows and around a second for a million.).

Sub DistinctCountWithRestrictions()

    Dim ws As Worksheet
    Dim arrListC As Variant
    Dim arrListH
    Dim dicList As Dictionary
    Dim lr As Long

    Set ws = ThisWorkbook.Worksheets("Sheet3")
    Set dicList = New Dictionary

    With ws
        lr = .Cells(.Rows.Count, "C").End(xlUp).Row
        arrListC = .Range("C2:C" & lr)
        arrListH = .Range("H2:H" & lr)
        For i = 1 To UBound(arrListC)
            If arrListC(i, 1) <> "" And arrListH(i, 1) <> "" Then dicList.Item(Key:=arrListC(i, 1)) = 1
        Next
        .Cells(1, "C").Value = dicList.Count
      End With

End Sub

 

[Edwardryez]

In fact, by removing the "exists" check it runs even quicker (0.6 seconds for 100,000 rows and around a second for a million.).

Sub DistinctCountWithRestrictions()

    Dim ws As Worksheet
    Dim arrListC As Variant
    Dim arrListH
    Dim dicList As Dictionary
    Dim lr As Long

    Set ws = ThisWorkbook.Worksheets("Sheet3")
    Set dicList = New Dictionary

    With ws
        lr = .Cells(.Rows.Count, "C").End(xlUp).Row
        arrListC = .Range("C2:C" & lr)
        arrListH = .Range("H2:H" & lr)
        For i = 1 To UBound(arrListC)
            If arrListC(i, 1) <> "" And arrListH(i, 1) <> "" Then dicList.Item(Key:=arrListC(i, 1)) = 1
        Next
        .Cells(1, "C").Value = dicList.Count
      End With

End Sub

RickXL, That was going to be my next future question in June

 

[RickXL]

RickXL, That was going to be my next future question in June

Note to self: Add clairvoyance to CV

 

[Edwardryez]

Cant do that Better i add I Know RickXL to CV