SUMIFS with multiple criteria and OR logic

SUMIFS with multiple criteria and OR logic
Example

Related Functions

=SUM(SUMIFS(sum_range,criteria_range,{"black","red"}))

To sum based on multiple criteria using OR logic, you can use the SUMIFS function with an array constant.

In the example shown, the formula in H6 is:

=SUM(SUMIFS(E4:E11,D4:D11,{"complete","pending"}))

How this formula works

By default, the SUMIFS function only allows AND logic – when you provide multiple conditions, all conditions must match to be included in the result.

One solution is to supply multiple criteria in an array constant like this:

{“complete”,”pending”}

This will cause SUMIFS to return two results: a count for “complete” and a count for “pending”, in an array result like this:

{116,33}

To get a final total, we wrap SUMIFS inside SUM. The SUM function sums all items in the array and returns the result.

With wildcards

You wildcards in the criteria if needed. For example, to sum items that contain “black” or “red” anywhere in the the criteria_range, you can use:

=SUM(SUMIFS(sum_range,criteria_range,{“*black*”,”*red*”}))

Don’t double-count

Whenever you use multiple criteria with OR logic, be careful not to count two times, especially if the criteria include wildcards. In this example, the two criteria are mutually exclusive, but in other scenarios it is easy to accidentally double count by applying criteria that overlap.
 

0 votes. 0 / 5

Excel - Excel Functions - Excel Formulas
Logo