Counting selections in multiple drop down lists within a row

[Jamie6325]

I am trying to count the number of selections (not caring what they select) in multiple drop down lists on multiple columns. For example: If a person were to select 3 items from the drop down list in A2, and 4 items from the drop down list in B2, and 1 item from the drop down list in C2, I would want D2 to show 8.

 

[Micron]

You don't mean simultaneous selections right? That is, not dropping down and selecting 3 items in the list in one go.
You mean selecting one item, then the list closes, then choose another and the list closes, and so on? Then at the top of the sheet code module have

Dim intCount As Integer (right under Option Explicit)

In the AfterUpdate event of each combo, have it increment intCount by 1:

Private myCombo1_AfterUpdate()
intCount = intCount +1
End Sub

If they choose the same item 3 times, the count will increment same as if they chose different values.

 

[Jamie6325]

You don't mean simultaneous selections right? That is, not dropping down and selecting 3 items in the list in one go.
You mean selecting one item, then the list closes, then choose another and the list closes, and so on? Then at the top of the sheet code module have

Dim intCount As Integer (right under Option Explicit)

In the AfterUpdate event of each combo, have it increment intCount by 1:

Private myCombo1_AfterUpdate()
intCount = intCount +1
End Sub

If they choose the same item 3 times, the count will increment same as if they chose different values.

Here is the code I am currently using and I am sure it could be shortened. I also want to make it so you cannot select duplicates, but I have fixed it yet. Where exactly would your code fit in?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Target.Column = 8 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Application.EnableEvents = True

On Error GoTo Exitsub
If Target.Column = 9 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Application.EnableEvents = True

On Error GoTo Exitsub
If Target.Column = 10 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Application.EnableEvents = True

On Error GoTo Exitsub
If Target.Column = 11 Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
Target.Value = Oldvalue & ", " & Newvalue
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[Micron]

Where exactly would your code fit in?

I just checked and unlike Access, Excel has no after update event so I guess you can ignore that.
Methinks that if you expect others to help you out in forums you need to
a) answer the questions you're asked and
b) post code within vba code tags and use proper indentation
That's just a suggestion.

You're using sheet event, which is something I know little about so I'm going to bow out. Coming from Access vba I thought this was simple, but when combo events aren't the same between Office apps (which is crazy) it's not I guess. If I saw the code in your first post I wouldn't have troubled you, so sorry about that.

 

[Jamie6325]

I just checked and unlike Access, Excel has no after update event so I guess you can ignore that.
Methinks that if you expect others to help you out in forums you need to
a) answer the questions you're asked and
b) post code within vba code tags and use proper indentation
That's just a suggestion.

You're using sheet event, which is something I know little about so I'm going to bow out. Coming from Access vba I thought this was simple, but when combo events aren't the same between Office apps (which is crazy) it's not I guess. If I saw the code in your first post I wouldn't have troubled you, so sorry about that.

Micron,
Thank you for the tips for getting answers. This is my first time posting. I will make sure to use your suggestions in the future.

I ended using the formula below which works. The drop down lists were in columns H through K so in the column I (all in the same row - this example was 10) I pasted the formula and got correct number of selections.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(H10:K10,ROW(H10:K10)-ROW(H10:K10),0,1,1))*(LEN(H10:K10)-LEN(SUBSTITUTE(H10:K10,",",""))+(H10:K10<>"")))

 

[Micron]

I'm glad you found/came up with a solution!