How to SUM 2d ranges with SUMPRODUCT

by | May 30, 2018 | Excel Tips & Tricks

How to SUM 2d range by conditions with the SUMPRODUCT

Summing values by a condition may sound easy. If not, please see Crunch Data Even Faster with the SUMIFS Function article as well. You can easily sum values by condition(s) with SUMIF and SUMIFS functions; with a limitation. The sum range and criteria ranges should have equal sizes. Because 2d criteria range is not a common scenario, this article explains how to sum 2d ranges by conditions using SUMPRODUCT function.

Syntax

=SUMPRODUCT(sum range * (horizontal criteria range= horizontal criteria)*(vertical criteria range= vertical criteria))

Steps

  1. Use =SUMPRODUCT(
  2. Select or type the range reference that includes cells to add with an asterisk C3:H8*
  3. Add criteria range and criteria pair with an equal sign in brackets (C2:H2="Category 2")*
  4. Repeat the previous step with the next criteria (B3:B8="Level 3")
  5. Type ) and press Enter to complete the formula

How

The SUMPRODUCT function's ability to handle arrays provides an advantage over the limitations of SUMIF and SUMIFS functions. If you analyze the formula deeply, you realize that each criteria range and criteria equality pair returns Boolean arrays that contain TRUE/FALSE values.

(C2:H2="Category 2") returns {FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}

(B3:B8="Level 3") returns {FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}

Multiplying two opposite-directional arrays ((C2:H2="Category 2")*(B3:B8="Level 3")) does two things:

  1. Converts TRUE/FALSE values into 1/0 numerals respectively.
  2. Creates a 2-dimensional array (remember matrix algebra)

((C2:H2="Category 2")*(B3:B8="Level 3")) returns {0,0,0,0,0,0;0,0,0,0,0,0;0,1,0,0,1,0;0,0,0,0,0,0;0,1,0,0,1,0;0,0,0,0,0,0}

Note: Comma (,) represents columns and semicolon (;) represents rows.

Multiplying the sum range and 1/0 array also returns another array that contains only filtered values because other values are eliminated by 0's.

C3:H8*(C2:H2="Category 2")*(B3:B8="Level 3") returns {0,0,0,0,0,0;0,0,0,0,0,0;0,99,0,0,28,0;0,0,0,0,0,0;0,29,0,0,17,0;0,0,0,0,0,0}

The SUMPRODUCT function adds the values of its argument array.

=SUMPRODUCT(C3:H8*(C2:H2="Category 2")*(B3:B8="Level 3"))