excel - 使用 SUMIF 获取条件列表

Question

尝试使用 SUMIF 按类对数据进行分组：

我有一个如下所示的数据集：

我还有一个属性列表：

现在我想要随着时间的推移的总和Class A Class B。Class C

因此，在一个新列中(Cell I2)，我输入了公式：=SUMIF($B$1:$H$1,$N$2:$N$8,$B2:$H2)结果应该是116，但不是吗？

我究竟做错了什么？

标签： excelcsv

Answer 1

Five Ranges Madness

In Excel

In cell I2 use the following formula:

=SFive($B2:$H2,$B$1:$H$1,$N$2:$N$8,$I$1:$K$1,$O$2:$O$8)

and copy/paste where you need it.

In VBA

Function SFIVE(SumRange As Range, CompanyH As Range, CompanyV As Range, _
        ClassH As Range, ClassV As Range) As Double ' As Long if whole numbers.

    Dim vntSumH As Variant    ' Horizontal Sum Array
    Dim vntCompH As Variant   ' Horizontal Company Array
    Dim vntCompV As Variant   ' Vertical Company Array
    Dim vntClassH As Variant  ' Horizontal Class Array
    Dim vntClassV As Variant  ' Vertical Class Array
    Dim strClass As String    ' Current Class
    Dim strCompany As Variant ' Current Company

    Dim j As Long   ' Horizontal Class Range Column Counter
    Dim i As Long   ' Vertical Class and Vertical Company Array Row Counter
    Dim k As Long   ' Horizontal Company and Sum Array Row Counter

    vntSumH = SumRange    ' Copy Horizontal Sum Range to array.
    vntCompH = CompanyH   ' Copy Horizontal Company Range to array.
    vntCompV = CompanyV   ' Copy Vertical Company Range to array.
    vntClassH = ClassH    ' Copy Horizontal Class Range to array.
    vntClassV = ClassV    ' Copy Vertical Class Range to array.

    ' Loop through columns of Horizontal Class Range.
    For j = 1 To ClassH.Columns.Count
        ' Check column of "Input Cell" against column of Horizontal Class Range.
        If Application.ThisCell.Column = ClassH.Column + j - 1 Then
            ' Assign only Class (last character) to Current Class.
            strClass = Right(vntClassH(1, j), 1)
            ' Loop through rows of Vertical Class (Company) Array.
            For i = 1 To UBound(vntClassV)
                ' Check if value of current row in Vertical Class Array
                ' is equal to Current Class.
                If vntClassV(i, 1) = strClass Then
                    ' Assign value of current row in Vertical Company Array
                    ' to Current Company.
                    strCompany = vntCompV(i, 1)
                    ' Loop through columns of Horizontal Company (Sum) Array.
                    For k = 1 To UBound(vntCompH, 2)
                        ' Check if value of current column in Horizontal
                        ' Company Array is equal to Current Company.
                        If vntCompH(1, k) = strCompany Then
                            ' Check if current column in Sum Array contains
                            ' a number.
                            If IsNumeric(vntSumH(1, k)) Then
                                ' Add value of current column in Sum Array
                                ' to SFIVE.
                                SFIVE = SFIVE + vntSumH(1, k)
                            End If
                            ' If you will allow multiple occurrences of
                            ' a Company in Horizontal Company Array (Range),
                            ' you will have to outcomment the following
                            ' "Exit For" line.
                            Exit For
                        End If
                    Next
                End If
            Next
            Exit For ' Stop searching because these are unique values.
        End If
    Next

End Function