Dependent combobox to provide matching row count based on 3 criteria

These are active-x controls, you can populate them with code.

ActiveX codes are located in the sheet module, right click the sheet tab and select "View Code"

See attached, select A1 to populate the 1st combobox, then when you make a selection the second combox populates

The code looks like this,

Private Sub ComboBox1_Change()
    Dim ws As Worksheet
    Dim LstRw As Long
    Dim Rng As Range
    Dim c As Range

    Set ws = Sheets("Install Database")
    With ws
        LstRw = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set Rng = .Range("B4:B" & LstRw)
        Me.ComboBox2.Clear
        For Each c In Rng.Cells
            If c = Me.ComboBox1 Then
                Me.ComboBox2.AddItem c.Offset(, 1)
            End If
        Next c
    End With



End Sub

Private Sub ComboBox2_Change()
    Dim ws As Worksheet
    Dim LstRw As Long
    Dim Rng As Range
    Dim c As Range

    Set ws = Sheets("Install Database")
    With ws
        LstRw = .Cells(.Rows.Count, "C").End(xlUp).Row
        Set Rng = .Range("C4:C" & LstRw)
        Me.ComboBox3.Clear
        For Each c In Rng.Cells
            If c.Text = Me.ComboBox2.Value Then
                Me.ComboBox3.AddItem c.Offset(, 1)
            End If
        Next c
    End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address <> "$A$1" Then Exit Sub
    Dim cUnique As Collection
    Dim Rng As Range
    Dim Cell As Range
    Dim sh As Worksheet
    Dim vNum As Variant

    Set sh = ThisWorkbook.Sheets("Install Database")
    Set Rng = sh.Range("B4", sh.Range("B4").End(xlDown))
    Set cUnique = New Collection

    On Error Resume Next
    For Each Cell In Rng.Cells
        cUnique.Add Cell.Value, CStr(Cell.Value)
    Next Cell
    On Error GoTo 0
    Me.ComboBox1.Clear

    For Each vNum In cUnique
        Me.ComboBox1.AddItem vNum
    Next vNum

End Sub

Thank you for your response. I see what you mean by using code instead of formulas.

The second and third comboboxes populate with duplicate data when I select "Utah" in the first combobox for example. What would I change to make the list only show unique values?

Also, How would I go about populating the "Matching rows" count to tell me how many rows contain matching data whether 1,2, or 3 comboboxes are filled out?

You can change the combobox1 code

Private Sub ComboBox1_Change()
    Dim ws As Worksheet
    Dim LstRw As Long
    Dim Rng As Range
    Dim c As Range
    Dim cUnique As Collection
    Dim vNum As Variant

    Set cUnique = New Collection
    Set ws = Sheets("Install Database")
    With ws
        LstRw = .Cells(.Rows.Count, "B").End(xlUp).Row
        Set Rng = .Range("B4:B" & LstRw)
        Me.ComboBox2.Clear
        On Error Resume Next
        For Each c In Rng.Cells
            If c = Me.ComboBox1 Then
                cUnique.Add c.Offset(, 1).Value, CStr(c.Offset(, 1).Value)
            End If
        Next c
        On Error GoTo 0
    End With
    For Each vNum In cUnique
        Me.ComboBox2.AddItem vNum
    Next vNum


End Sub

Use this formula to find the matches of all three comboboxes, it uses the linked cells you have for the comboboxes

Formula:

=COUNTIFS('Install Database'!B4:B16,"="&C2,'Install Database'!C4:C16,"=" & I2,'Install Database'!D4:D16,"="&N2)

The combobox works perfectly now, thank you. The formula will only work if all 3 comboboxes are filled out. What I am trying to do is enter for example "Utah" and leave the other 2 boxes blank. The number should be 4. But if I put in "Utah" and "455" for the building while leaving the room blank the number would be 3 because there are 3 unique rooms in that building and so on. Maybe I am not explaining it right?

=COUNTIFS('Install Database'!B4:B16,"="&C2)
=COUNTIFS('Install Database'!B4:B16,"="&C2,'Install Database'!C4:C16,"=" & I2)
=COUNTIFS('Install Database'!B4:B16,"="&C2,'Install Database'!C4:C16,"=" & I2,'Install Database'!D4:D16,"="&N2)

I must not be explaining it right. I am not looking for options for 1, 2, or 3 counts. I am looking for one cell that will differentiate whether I have selected a location only or a location and a building or a location, building, and room. There are 3 examples in the sample file I posted at the beginning of this thread.

Ghostrider757 - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

You should be able to figure it out with the formulas I gave you.

Originally Posted by JohnTopley

Try ("generic " formula)

=COUNTIFS($A$1:$A$13,IF(D1<>"",D1,"*"),$B$1:$B$13,IF(E1<>"",E1,"*"),$C$1:$C$13,IF(F1<>"",F1,"*"))

See attached for example

Works ONLY (on my testing) if fields are defined as TEXT

John can it be more simple.

Like

Formula:

=MIN(IF(COUNTIFS(A1:C13,D1:F1)>0,COUNTIFS(A1:C13,D1:F1)))

CSE