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

- Use
**=SUMPRODUCT(** - Select or type the range reference that includes cells to add with an asterisk
**C3:H8*** - Add criteria range and criteria pair with an equal sign in brackets
**(C2:H2="Category 2")*** - Repeat the previous step with the next criteria
**(B3:B8="Level 3")** - 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:

- Converts
*TRUE/FALSE*values into*1/0*numerals respectively. - 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"))