The result is 61,600, the sum of Amount in column C5:C16 when the Status in D5:D16 is not blank.
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. In this case, we need to test for only one condition, which is that the cells in D5:D16 are not blank. The generic syntax for SUMIFS with one criteria looks like this: We start off with the sum_range, which contains Amount in C5:C16: Next, we add the range that we need to test, which in this case is Status, D5:D15: Finally, we add the criteria, which is the not equal to operator (<>), which must be enclosed in double quotes (""): The result is 61,600, the sum of Amount in column C5:C16 when Status in D5:D16 is not blank. The formula in cell G6, which sums values in C5:C16 when cells in D5:D16 are empty is: To read more about how to use the SUMIFS function with logical operators and wildcards, see this page.
SUMPRODUCT function
Another way to solve this problem is with the SUMPRODUCT function and a formula like this: This is an example of using Boolean logic in Excel. Th expression on the left checks if the 12 cells in D5:D16 are not empty: Because there are 12 cells in D5:D16, the expression returns an array of 12 TRUE and FALSE values: Note the TRUE values correspond with cells D5:D16 that are not blank. Next, the math operation of multiplying this array by the range C5:C16 automatically converts the TRUE and FALSE values to an array of 1s and 0s. Inside SUMPRODUCT, we now have: 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: 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: 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 values in D5:D16 are not empty. The result inside SUM looks like this: The final result is 61,600. For more on the FILTER function, see this page.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
