Explanation

In this example, the goal is to sum the values in F5:F16 when the Color in C5:C16 is “Red” and the State in D5:D16 is “TX”. This is an example of a conditional sum with multiple criteria and the SUMIFS function is the easiest way to solve this problem.

SUMIFS function

The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria . To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The syntax for the SUMIFS function depends on the number of conditions needed. Each separate condition will require a range and a criteria . The generic syntax for SUMIFS looks like this:

=SUMIFS(sum_range,range1,criteria1) // 1 condition
=SUMIFS(sum_range,range1,criteria1,range2,criteria2) // 2 conditions

The first argument , sum_range , is the range of cells to sum, which should contain numeric values. Notice the conditions (called criteria ) are entered in pairs . Each new condition requires a separate range and criteria .

Example - Red and TX

We start off with sum_range, which is the range F5:F16:

=SUMIFS(F5:F16,

Next, we add the first condition, which is that the Color in C5:C16 is “Red”:

=SUMIFS(F5:F16,C5:C16,"red"

The range is C5:C16, and the criteria is “red”. Note that SUMIFS is not case-sensitive, so “red” will match “Red”, “RED”, and “red”. Next, we need to add the second condition, which is that the State in D5:D16 is “TX”. Again, we need to supply both a range and a criteria:

=SUMIFS(F5:F16,C5:C16,"red",D5:D16,"tx")

When the formula is entered, the result is $88.00, the sum of the Total in F5:F16 when the Color in C5:C16 is “Red” and the State in D5:D16 is “TX”.

Example - Red and >20

Next, the goal is to sum the Total in F5:F16 when the color is “red” and the value of Total is greater than $20. This means we need to include the logical operator (>) in the second criteria. The formula in I7 is:

=SUMIFS(F5:F16,C5:C16,"red",F5:F16,">20")

Explanation

In this example, the goal is to sum the Amounts in C5:C16 when the Lead in D5:D16 is not blank (i.e., not empty). A good way to solve this problem is to use the SUMIFS function . However, you can also use the SUMPRODUCT function or the FILTER function , as explained below. Because SUMPRODUCT and FILTER can work with ranges and arrays , they are more flexible.

Background study

  • How to use the SUMIFS function
  • Boolean operations in array formulas
  • FILTER function basic example

SUMIFS Function

The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria . To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The generic syntax for SUMIFS with one condition looks like this:

=SUMIFS(sum_range,range1,criteria1) // 1 condition

In this case, we need to test for only one condition, which is that the cells in D5:D16 are not blank . We start off with the sum_range , which contains the amounts in C5:C16:

=SUMIFS(C5:C16,

Next, we add the range that we need to test, which in D5:D16:

=SUMIFS(C5:C16,D5:D16,

Finally, we add the criteria , which is not equal to operator (<>), which must be enclosed in double quotes (""):

=SUMIFS(C5:C16,D5:D16,"<>")

SUMPRODUCT function

Another way to solve this problem is with the SUMPRODUCT function and a formula like this:

=SUMPRODUCT((D5:D16<>"")*C5:C16)

This is an example of using Boolean logic in Excel. The expression on the left checks if the cells in D5:D16 are not empty:

(D5:D16<>"")

Because there are 12 cells in D5:D16, the expression returns an array of 12 TRUE and FALSE values:

{TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE}

Note the TRUE values correspond to cells in D5:D16 that are not blank . Next, the math operation of multiplying this array by the range numeric values in C5:C16 automatically converts the TRUE and FALSE values to an array of 1s and 0s. Inside SUMPRODUCT, we can visualize the operation like this:

=SUMPRODUCT({1;0;1;1;1;1;0;1;0;1;1;0}*C5:C16)

In this form, you can see how the logic works. When the Boolean array is multiplied by C5:C16, it acts like a filter that only allows values associated with 1s to pass through; other values are “zeroed out”. After multiplication, we have one array:

=SUMPRODUCT({10600;0;12000;7500;6000;4000;0;7000;0;2500;12000;0})

With only one array to process, SUMPRODUCT sums the array and returns 61,600 as a final result. One advantage of SUMPRODUCT is that it can handle array operations natively. This can be handy when you want to adjust a formula to use more specific logic that is not supported by SUMIFS. For more information, see Why SUMPRODUCT?

FILTER function

In the latest version of Excel, another approach is to use the FILTER function with the SUM function in a formula like this:

=SUM(FILTER(C5:C16,D5:D16<>"",0))

In this formula, we are literally removing values we don’t want to sum. The FILTER function is configured to return only values in C5:C16 when cells in D5:D16 are not empty . The result inside SUM looks like this:

=SUM({10600;12000;7500;6000;4000;7000;2500;12000})

The final result is 61,600. Like SUMPRODUCT, FILTER is a more flexible function that can apply criteria in ways that SUMIFS can’t. For more on the FILTER function, see this page .

Sum if blank

The formulas above can be easily adjusted to sum amounts when corresponding cells in D5:D16 are blank . In the worksheet shown, the formula in cell G6 is:

=SUMIFS(C5:C16,D5:D16,"")

The result is 32,700, the sum of amounts in C5:C16 when corresponding cells in D5:D16 are blank . The equivalent SUMPRODUCT and FILTER formulas are as follows:

=SUMPRODUCT((D5:D16="")*C5:C16)
=SUM(FILTER(C5:C16,D5:D16="",0))

These formulas also return 32,700.