A good way to solve this problem is to use theSUMIFS function.
However, you could also use theSUMPRODUCT functionor theFILTER function, as explained below.
Because SUMPRODUCT and FILTER can work with rangesandarrays, they are more flexible.
The main challenge with SUMIFS is thequirky syntax.
For criteria, we simply use the “not equal to” operator, “<>”.
“not blank”.
To read more about how to use the SUMIFS function with logical operators and wildcards,see this page.
One advantage of SUMPRODUCT is that it can handlearray operationsnatively.
For more information, seeWhy SUMPRODUCT?
The result inside SUM looks like this:
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.
The equivalent SUMPRODUCT and FILTER formulas are as follows:
These formulas also return 32,700.
SUMPRODUCT Function
The Excel SUMPRODUCT function multipliesrangesorarraystogether and returns the sum of products.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
