SumProduct over sets of cells (not contiguous)

Question

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?

Answer 1

I agree with the comment "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"

A possible solution is to embed the CHOOSE() function within your SUMPRODUCT (this trick actually is pretty handy for vlookups, finding conditional maximums, etc.).

Example: Let's say your data has eight observations and is in two columns (columns B and C) but you don't want to include some observations (exclude observations in rows 4 and 5). Then the SUMPRODUCT code looks like this...

=SUMPRODUCT(CHOOSE({1,2},A1:A3,A6:A8),CHOOSE({1,2},B1:B3,B6:B8))

I actually thought of this on the fly, so I don't know the limitations and as you can see it is not that pretty.

Hope this helps! :)

Answer 2

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:

• Excel enumerates ranges by column then row so if you have a continuous range where the data is organized by column, you have to select separate ranges: gvSUMPRODUCT(A1:A10,B1:B10) and not gvSUMPRODUCT(A1:B10).
• The function works by pairwise multiplying the first half of cells with the second and then summing those products: gvSUMPRODUCT(A1,C3,L2,B2,G5,F4) = A1*B2 + C3*G5 + L2*F4. I.e. order matters.
• You could extend the function to include n-wise multiplication by doing something like gvNSUMPRODUCT(n,ranges).
• If there are an odd number of cells (not ranges), it returns the #VALUE error.