多选项下拉列表适配SUMIFS函数问题及扩展需求

问题1

当E16同时选中多个值（如VN和BD）时，B16返回0，SUMIFS函数无法正常工作。已知手动写成=SUM(SUMIFS($B$7:$B$14;$C$7:$C$14;C16;$D$7:$D$14;D16;$E$7:$E$14;{"VN";"BD"}))可以实现，但数据量过大无法手动枚举，求适配下拉列表的解决方法。

问题2

是否可以为sex、type、state三个列的下拉列表都启用多选功能，并适配B16的求和公式？

解决方案 #

针对问题1：适配E16多选的求和公式

将B16的公式替换为以下内容，自动拆分E16中的多选项为匹配数组：

=SUM(SUMIFS($B$7:$B$14;$C$7:$C$14;C16;$D$7:$D$14;D16;$E$7:$E$14;TRIM(MID(SUBSTITUTE(E16,"; ",REPT(" ",100)),ROW(INDIRECT("1:"&LEN(E16)-LEN(SUBSTITUTE(E16,"; ",""))+1))*100-99,100))))

核心逻辑：

• 用SUBSTITUTE把E16里的分隔符; 替换成重复空格，方便后续拆分
• MID+ROW生成序列，逐个提取每个选项内容
• TRIM清除选项前后的冗余空格
• 外层SUM汇总SUMIFS逐个匹配多选项后的结果
• 注意：如果你的多选分隔符不是; ，需要同步替换公式中的对应内容

针对问题2：三列启用多选并适配求和公式

1. 修改VBA实现三列多选

替换原VBA代码为以下版本，同时监听C16、D16、E16的多选操作：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Oldvalue As String
    Dim Newvalue As String
    Dim TargetRng As Range
    
    Set TargetRng = Union(Range("C16"), Range("D16"), Range("E16"))
    Application.EnableEvents = True
    On Error GoTo Exitsub
    
    If Not Intersect(Target, TargetRng) Is Nothing Then
        If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
            GoTo Exitsub
        Else
            If Target.Value = "" Then GoTo Exitsub
            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
    
Exitsub:
    Application.EnableEvents = True
End Sub

2. 修改求和公式适配三列多选

将B16公式升级为以下内容，支持三列的多选项匹配：

=SUM(SUMIFS($B$7:$B$14;
            $C$7:$C$14;TRIM(MID(SUBSTITUTE(C16,"; ",REPT(" ",100)),ROW(INDIRECT("1:"&LEN(C16)-LEN(SUBSTITUTE(C16,"; ",""))+1))*100-99,100));
            $D$7:$D$14;TRIM(MID(SUBSTITUTE(D16,"; ",REPT(" ",100)),ROW(INDIRECT("1:"&LEN(D16)-LEN(SUBSTITUTE(D16,"; ",""))+1))*100-99,100));
            $E$7:$E$14;TRIM(MID(SUBSTITUTE(E16,"; ",REPT(" ",100)),ROW(INDIRECT("1:"&LEN(E16)-LEN(SUBSTITUTE(E16,"; ",""))+1))*100-99,100)))

简化方案（适用于Excel 365/2021）：
如果使用的是较新版本Excel，可用TEXTSPLIT直接拆分多选项，公式更简洁：

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

TEXTSPLIT会直接将分隔字符串拆分为数组，无需复杂的拆分逻辑，执行效率更高。

内容的提问来源于stack exchange，提问作者Link