excelworksheet-function

SumProduct over sets of cells (not contiguous)


I have a total data set that is for 4 different groupings. One of the values is the average time, the other is count. For the Total I have to multiply these and then divide by the total of the count. Currently I use:

=SUM(D32*D2,D94*D64,D156*D126,D218*D188)/SUM(D32,D94,D156,D218)

I would rather use a SumProduct if I can to make it more readable. I tried to do:

=SUMPRODUCT((D2,D64,D126,D188),(D32,D94,D156,D218))/SUM(D32,94,D156,D218)

But as you can tell by my posting here, that did not work. Is there a way to do SumProduct like I want?


Solution

  • It might be possible with masterful excel-fu, but even if it can be done, it's not likely to be more readable than your original solution. The problem is that even after 20+ years, Excel still borks discontinuous ranges. Naming them won't work, array formulas won't work and as you see with SUMPRODUCT, they don't generally work in tuple-wise array functions. Your best bet here is to come up with a custom function.

    UPDATE

    You're question got me thinking about how to handle discontinuous ranges. It's not something I've had to deal with much in the past. I didn't have the time to give a better answer when you asked the question but now that I've got a few minutes, I've whipped up a custom function that will do what you want:

    Function gvSUMPRODUCT(ParamArray rng() As Variant)
    
        Dim sumProd As Integer
        Dim valuesIndex As Integer
        Dim values() As Double
    
        For Each r In rng()
            For Each c In r.Cells
                On Error GoTo VBAIsSuchAPainInTheAssSometimes
                    valuesIndex = UBound(values) + 1
                On Error GoTo 0
                ReDim Preserve values(valuesIndex)
                values(valuesIndex) = c.Value
            Next c
        Next r
        If valuesIndex Mod 2 = 1 Then
            For i = 0 To (valuesIndex - 1) / 2
                sumProd = sumProd + values(i) * values(i + (valuesIndex + 1) / 2)
            Next i
            gvSUMPRODUCT = sumProd
            Exit Function
        Else
            gvSUMPRODUCT = CVErr(xlErrValue)
            Exit Function
        End If
    
    VBAIsSuchAPainInTheAssSometimes:
        valuesIndex = 0
        Resume Next
    
    End Function
    

    Some notes: