Dependent combobox to provide matching row count based on 3 criteria

[Ghostrider757]

I am looking to count the number of rows that match the criteria that will fall in linked cells for each of 3 comboboxes. I did not want to go the data validation route as I need the drop down to be clearly visible at all times and not when a cell is clicked.

1. How would I dynamically be able to populate the combo boxes based on the previous selection(dependency)?

2. How would I be able to count rows that match criteria on another sheet if 1, 2, or 3 columns match.

I have provided an example spreadsheet. Any help is appreciated. I tried using SUMIFS, SUM(PRODUCT, and COUNTIFS but they did not produce the desired result. Any help is appreciated.

[davesexcel]

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,

Please Login or Register  to view this content.

[Ghostrider757]

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?

[davesexcel]

You can change the combobox1 code

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[Ghostrider757]

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?

[davesexcel]

Please Login or Register  to view this content.

[Ghostrider757]

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.

[AliGW]

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.

[davesexcel]

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

[Ghostrider757]

If I was able to figure it out, I wouldn't be asking for help. The formulas you provide are for 3 separate cells and similar to the one I already have on my master copy. I need one formula for one cell that will provide a result based on all 3 scenarios. I really appreciate the help but it seems we are going in circles.

[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

[shukla.ankur281190]

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

Please Login or Register  to view this content.

CSE