Equivalent of SUMDIVIDE in Excel

Problem

We want to create the equivalent of the function SUMDIVIDE in Excel, returning the sum of the division of ranges or arrays, the inverse of SUMPRODUCT.

Solution

With SUMPRODUCT we just use the inverse of the multiplication for the array acting as denominator: 1/array as denominator. In our example we return the sum of the division of column 1 by column 2.

=SUMPRODUCT(A2:A6,1/B2:B6)

Another option would be using SUM as an array formula.

{=SUM(A2:A6/B2:B6)}

Return the sum of multiple columns with SUMPRODUCT

Problem

For a given lookup value we want to return the sum of multiple columns. In our table, for Year 1 we'd like to return the sum of January, February, April, and December.

Solution

1. We use the function SUMPRODUCT with multiple AND (asterisk: *) and OR (plus: +) criteria.

=SUMPRODUCT((B2:M4)*(A2:A4=B7)*
      ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11)))

First we pass the range we want to sum (B2:M4), and then add the conditions: 1) Year 1 (A2:A4=B7); and 2) Months to sum: ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11))). In English, the year must be equal to Year 1 and the months must be January or February or April or December.

Related posts

• Return the sum of multiple columns with VLOOKUP

Return the sum of multiple columns with VLOOKUP

Problem

For a given lookup value we want to return the sum of multiple columns. In our table, for Year 1 we'd like to return the sum of January, February, April, and December.

Solution

1. With VLOOKUP we pass within the third argument col_index_num the array of columns we'd like to sum: {2,3,5,13}, always enclosed in curly brackets. This will return an array of 4 values {98,52,75,60}. :


{=VLOOKUP(B7,A1:M4,{2,3,5,13},0)}

2. To sum the elements of the array, we use the function sum and then press CTRL + SHIFT + ENTER or we can use the function SUMPRODUCT that doesn't require CTRL + SHIFT + ENTER.

{=SUM(VLOOKUP(B7,A1:M4,{2,3,5,13},0))}
=SUMPRODUCT(VLOOKUP(B7,A1:M4,{2,3,5,13},0))

3. An different approach would be to use SUMPRODUCT with multiple OR criteria described in this post.

Related posts

• Return the sum of multiple columns with SUMPRODUCT