Conditional Summing Tricks

Hello, Excel enthusiasts! Welcome to another learning-filled blog post where we deepen our understanding of Excel. Today, we will dive into some phenomenal conditional summing tricks resulting from a question I recently received: Can you create a formula that takes into account only columns that contain a certain word? For example, is it possible to create a sum of the “amounts” marked as open? Let’s get to it!

Video

Detailed Step-by-Step Walkthrough

We’ll walk through three exercises to illustrate different capabilities of the SUMIFS function.

Exercise 1: SUMIFS Basics

Firstly, let’s start with a basic case. We have a bunch of data transactions like this:

We want to sum amounts marked as Open. We’ll use the SUMIFS function for this.

Note: SUMIF could also be used here as there is only a single condition. However, in practice, I prefer to stick with SUMIFS consistently because down the road it is easy to add an additional condition if needed. Plus, the order of the arguments are reversed between the functions so it is easier for me personally to just stick with SUMIFS.

The first argument of the SUMIFS function is the range of numbers we want to add. The next two arguments define the condition. It is the criteria range followed by the criteria value.

So, we can use the following formula to create a sum of all Open transactions:

=SUMIFS(C12:C20, D12:D20, "Open")

Note: if we had entered the value Open into a cell, such as B7, we could use the cell reference rather than typing the criteria value manually into the formula, like this:

=SUMIFS(C12:C20, D12:D20, B7)

So, that is how we can use the SUMIFS function to sum the amounts for the Open transactions. But, what if we wanted to include both Open and Pending transactions? Well, let’s tackle that in the next exercise.

Exercise 2: OR Logic

What if we have multiple conditions, say, we want to sum amounts marked as Open or Pending? When we provide multiple conditions in the SUMIFS function, AND logic is used. This just means that all conditions must be true for that row to be included in the sum. So, if we attempted to sum Open or Pending values with the following formula, it would return 0:

=SUMIFS(C12:C20, D12:D20, "Open", D12:D20, "Pending")

It returns 0 because AND logic is applied, and D12:D20 can not be both Open and Pending.

So, what are we supposed to do? Do it manually? No worries, we can simulate OR logic by simply adding the results of multiple SUMIFS functions. For each unique condition (Open or Pending), we use a separate SUMIFS function and add them with the addition operator + like this:

=SUMIFS(C12:C20, D12:D20, "Open") + SUMIFS(C12:C20, D12:D20, "Pending")

With that, let’s move to our final exercise.

Exercise 3: Wildcard

Say we have a range of data with subtotals, and it looks something like this:

We would like to add all of the rows that begin with the word Subtotal. We can use SUMIFS and its ability to do a partial match. To accomplish this, we use the wildcard asterisk * like this:

 =SUMIFS(D10:D21, B10:B21, "Subtotal*")

This tells the function to add up the column D values, and include only those rows where the value in column B begins with the word Subtotal.

If on the other hand you wanted to match cells that end in Subtotal, change the formula to this:

 =SUMIFS(D10:D21, B10:B21, "*Subtotal")

And if you want to match cells that contain the word Subtotal change the formula to this:

 =SUMIFS(D10:D21, B10:B21, "*Subtotal*")

And that is how you do a partial match.

Summary of the Process

Leveraging the SUMIFS function in Excel can make conditional summing a breeze! We’ve seen how to sum the amount column values based on the status column’s content, used multiple SUMIFS for OR logic, and even included wildcards to perform a partial match.

If you have any alternatives, questions, or enhancements, please share by posting a comment below!

File Download

Want to put your learning into practice? Download our exercise Excel file and test your new knowledge!

FAQs:

Q: Can I use SUMIFS function for multiple conditions?

A: Yes, you can use multiple conditions with the SUMIFS function. To do so, add a pair of arguments for each condition. First the criteria range, and then the criteria value.

Q: What logic does the SUMIFS function use?

A: The SUMIFS function uses AND logic by default, meaning all conditions must be true for the row to be included in the total.

Q: How do I use OR logic in SUMIFS?

A: To use OR logic in SUMIFS, break your formula into separate SUMIFS functions for each condition and add them with the addition operator +.

Q: How do I include rows that contain a specific word, even if there are other characters before or after the word?

A: Use a wildcard (asterisk) before and after the criteria value argument like this: “*Subtotal*”

Q: Can the SUMIFS function be used to sum based on text conditions?

A: Yes, the SUMIFS function works for both numeric and text conditions.

Q: Can I add up multiple columns with the same condition?

A: Yes, use separate SUMIFS functions for each column.

Q: Can I use more than one wildcard in a SUMIFS function?

A: Yes, you can use as many wildcards as necessary.

Q: Do I have to manually type in the condition into the SUMIFS function?

A: No, you can also point the function to a cell containing the condition.

Q: Is there a limit on how many conditions SUMIFS function can handle?

A: Technically, Excel allows up to 127 conditions. However, simpler and fewer conditions are recommended for readability and performance reasons.