CODEWITHSUNDEEP
excelvbavalidationexcel-2010sumifs
Link
Link

Reputation: 1

Put multiple drop down list value into sumifs function

My data Example:

In B16 =SUMIFS($B$7:$B$14;$C$7:$C$14;C16;$D$7:$D$14;D16;$E$7:$E$14;E16) In row 16 below sex/type/state (C16/D16/E16) i create a dropdown and i have enable select multiple item for State(E16) list with this VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("E16")) Is Nothing 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
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & "; " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

1/ Now if i select multiple value like :VN and BD at the same time the return value in B16 = 0, the sumifs function not work, i know this function can do if i change the code in B16 into: =SUM(SUMIFS($B$7:$B$14;$C$7:$C$14;C16;$D$7:$D$14;D16;$E$7:$E$14;{"VN";"BD"}))
but my data table is too big to write it all so i have to use dropdown list. How can i fix this? 2/ Does it possible to enable select multiple item for sex,type,state to put value into B16 code? Thank you.

Upvotes: -1

Views: 69

Answers (1)

MGonet
MGonet

Reputation: 2047

Since you are using VBA anyway, the simplest solution in this case seems to be to define a simple UDF function (in the standard module):

Function ToArray(list As String)
   ToArray = Split(list, "; ")
End Function

And using it in the SUMIFS formula:

=SUM(SUMIFS($B$7:$B$14;$C$7:$C$14;C16;$D$7:$D$14;D16;$E$7:$E$14;ToArray(E16)))

Besides, when it comes to the event procedure, its logical structure can be simplified a bit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String, Newvalue As String
On Error GoTo Exitsub
If Not Intersect(Target, Range("E16")) Is Nothing Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  ElseIf Target.Value = "" Then
    GoTo Exitsub
  Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = "" Then
      Target.Value = Newvalue
    ElseIf InStr(1, Oldvalue, Newvalue) = 0 Then
      Target.Value = Oldvalue & "; " & Newvalue
    End If
  End If
End If
Exitsub:
Application.EnableEvents = True
End Sub

Upvotes: 0

Related Questions