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:
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.
