Using =sumproduct() in Google Spreadsheets

It has been a while since I posted but I promise to have some exciting posts soon!  But for today I thought I would show a quick example of a way to flip sumif behavior.

Consider a situation where all odd number rows have names and all even number rows have a numerical value in a single column.  In my example, I either have Ted or Adam in the odd number rows.  The goal here is to find the sum of the values directly below each time Ted appears and to find the sum of the values directly below each time Adam appears.



Formula in D1: =SUMPRODUCT(N(A$1:A$19=C1),A$2:A$20)+INDEX(0,1)
Formula in D2: =ARRAYFORMULA(SUMPRODUCT(N(A$1:A$19=C2),A$2:A$20))

What does sumproduct() do?
Sum product takes up to ??? (documentation says 30 but I think it is closer to 255) ranges (in Google Spreadsheets) or 255 ranges (in Excel) and multiplies the corresponding values together and then sums up all of the values.  If my function was =sumproduct({2,3,4},{1,2,3}) the result would be 20 (2*1 + 3*2 + 4*3).  In order for the sum and multiplication to work we have to turn our criteria into a number.  Therefore we use the N() function to turn a value into 1 if the criteria is met or 0 if not.  The second step is to define the range we want to multiply the first range by and that is basically the same range we had before but shifted down a cell.  Instead of A1:A19 it becomes A2:A20.

To visualize what is going on see the tab called Visualize in the example or watch the video below.

Now that we have defined our ranges to be multiplied, the function is ready to go!  Since SUMPRODUCT must be array enabled to work we must apply an array-enabler.  I've illustrated two ways to do this depending on whether you are using Google Spreadsheets, Excel, or both.  By wrapping the whole function with arrayformula() you enable the function to do array calculation.  You can also do this by adding index(0,1) which when evaluated is always zero and has no effect on the function, but enables the array calculation for Google Spreadsheets and Excel.  Arrayformula() is not supported by Excel and you will see a #NAME? error.

Enjoy and feel free to add any questions in the comments.

Also, see the video explanation recorded as a Google Hangout On Air and feel free to make a copy of the Spreadsheet to explore.

Cheers!