SumProduct over sets of cells (not contiguous)

excel worksheet-function
Craig picture Craig · Mar 10, 2010 · Viewed 28.1k times · Source

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

Chris picture Chris · Mar 24, 2014

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! :)

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More