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.
Explanation
In this example, the goal is to sum the numbers in column E when the item in column B appears in the range G5:G7. The named range things is not required. It is used only for convenience and can be expanded as needed to include additional criteria. The article below explains several ways to solve this problem.
SUMIFS with SUMPRODUCT
One way to accomplish this is to give the SUMIFS function all three values in the named range things (G5:G7) as criteria, then use the SUMPRODUCT function to calculate a total. This is the approach in the worksheet shown, where the formula in I5 is:
=SUMPRODUCT(SUMIFS(E5:E16,B5:B16,things))
Working from the inside out, the SUMIFS function takes three arguments: sum_range , range , and criteria.
- Sum_range is E5:E16 and contains the values we want to sum.
- Range is B5:B15 and contains the values we are testing.
- Criteria is the named range things (G5:G7), which contains 3 values.
When Excel evaluates this formula, it retrieves the values in things as an array like this:
=SUMPRODUCT(SUMIFS(E5:E16,B5:B16,{"apples";"pears";"kiwis"}))
Because SUMIFS receives 3 separate values for criteria , it returns 3 results in an array like this:
{3.75;11.25;4.5}
The first number is a sum for “apples”, the second number is a sum for “Pears”, and the last number is a sum for “Kiwis”. These results are returned directly to the SUMPRODUCT function like this:
=SUMPRODUCT({3.75;11.25;4.5})
With just one array to process, SUMPRODUCT sums the array and returns a final result of $19.50.
ISNUMBER and MATCH
The above formula works fine, but has some limitations due to the nature of SUMIF . As an alternative, you can use the formula below, which uses the ISNUMBER function with the MATCH function to achieve the same result:
=SUMPRODUCT(--ISNUMBER(MATCH(B5:B16,things,0)),E5:E16)
This is a more flexible formula in cases where logical conditions become more complex. This formula shows one example .
FILTER function
This problem can also be solved in a more literal way with the FILTER function like this:
=SUM(FILTER(E5:E16,ISNUMBER(MATCH(B5:B16,things,0)),0))
In this formula, FILTER is configured to use ISNUMBER + MATCH to extract the Price of Items that appear in the named range things . The result is delivered to the SUM function which returns a final result. See this example for more details.
