Skip to content

How to Use SUMIFS with OR Criteria in Excel

In this article, I will show you how to use the SUMIFS function with OR criteria in Excel.

SUMIFS Default Behavior

By default, the SUMIFS function adds the numbers for those records containing values that meet multiple criteria.

For example, you can use the SUMIFS function to add the prices for those products that are both Red AND Large.

=SUMIFS(D2:D8, B2:B8, "Red", C2:C8, "Large")

However, what if you wanted to add the prices for those products that are either Red OR Blue? Or perhaps those that are either Red OR Blue AND Large?

Let’s take a look at both of these scenarios.

OR Criteria Example 1

Let’s say you want to calculate the sum for those products that are either Red OR Blue.

Since Red and Blue belong to the same column or field, you can use the following formula.

=SUM(SUMIFS(D2:D8, B2:B8, {"Red", "Blue"}))

You place the two possible values (Red and Blue) in an array for the criteria argument. And as a result, the SUMIFS function calculates an array of totals – one for the color Red and one for the color Blue.

=SUM( {45, 40} )

This array passes to the SUM function, which calculates the final total.

OR Criteria Example 2

Now let’s say you want to add the prices for those products that are either Red OR Blue AND Large.

You would enter the following formula.

=SUM(SUMIFS(D2:D8, B2:B8, {"Red", "Blue"}, C2:C8, "Large"))

The SUMIFS function calculates two totals.

  • The total for those products that are Red AND Large
  • The total for those products that are Blue AND Large
=SUM( {30, 25} )

Finally, the SUM function adds these two numbers together to give you the final total.

And that is how to use the SUMIFS function with OR criteria in Excel.

This Post Has One Comment

  1. It’s the comma separating the third criteria that makes this work, right? I didn’t realize that this was something you could do. I would love to see a video or practice file about this. I feel like I kind of get it, but might need a little more to really get it!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top