Exclude Multiple Criteria in Same Column with SUMIFS Function

The SUMIFS function in Microsoft Excel evaluates the sum of a range of cells under multiple conditions. If you are looking for special tricks to exclude multiple criteria in the same column using the SUMIFS function in Excel, you’ve come to the right place. There are numerous ways to exclude multiple criteria in the same column using the SUMIFS function in Excel. This article will discuss the details of these methods. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


6 Suitable Examples to Exclude Multiple Criteria in Same Column Using SUMIFS Function

The following section will use six effective and tricky methods to exclude multiple criteria in the same column using the SUMIFS function in Excel. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Excluding Multiple Criteria

Here, we will demonstrate how to exclude multiple criteria in the same column using the SUMIFS function. For this example, let’s assume we have a dataset of some fruits with their prices. Now we will show how to find the total costs of all the fruits except Oranges and Bananas.

using SUMIFS function to Exclude Multiple Criteria in Same Column

Let’s walk through the following steps to do the task.

📌 Steps:

  • First of all, select the cell you want to put the value in (cell F5).
  • Then write down the following formula in it.

=SUMIFS(C5:C13,B5:B13,"<>Oranges",B5:B13,"<>Bananas")

The SUMIFS function finds the rows of fruits except for oranges and bananas. As soon as the matched rows are found, the prices are added up and a result is displayed.

  • Next, press Enter.
  • Consequently, you will get the total prices in cell F5.

get the output

Read More: How to Use SUMIFS Function in Excel with Multiple Criteria


2. Excluding Multiple Criteria Based on Wildcard Characters

Use of the wildcard characters (*,?, ) will enable you to locate the precise text value that you might not be able to recall right away. For instance, we are interested in the total costs of some fruits whose names begin with “Apples” and “Lemons”. For the purpose of calculation, we will combine SUM and SUMIFS functions.

Excluding Multiple Criteria Based on Wildcard Characters

Let’s walk through the following steps to do the task.

📌 Steps:

  • First of all, select the cell you want to put the value in (cell F5).
  • Then write down the following formula in it.

=SUM(SUMIFS(C5:C13,B5:B13,{"*Apples","*Lemons"}))

  • Next, press Enter.
  • Consequently, you will get the total prices in cell F5.

🔎 How Does the Formula Work?

♣️ Formula: SUM(SUMIFS(C5:C13,B5:B13,{“*Apples”,”*Lemons”}))

👉 Here in the SUMIF function instead of giving the total string or text, we have used “*Apples” and “*Lemons” to find the fruit name which will be matched with the last name with this.

👉 Then all the prices will be summed up to get the total price using the SUM function.

Read More: SUMIFS with Wildcard in Excel (+ Alternative Formulas)


3. Using SUMIFS with Multiple Dates Criteria

We will now show you how to use dates by applying TODAY and SUMIFS functions. Consider for the purposes of this example that we have a dataset of some fruits with the delivery date and quantity. We’ll now give an example of how to calculate the total number of deliveries made over the previous 60 days.

Using SUMIFS with Multiple Dates Criteria

Let’s walk through the following steps to do the task.

📌 Steps:

  • First of all, select the cell you want to put the value in (cell G5).
  • Then write down the following formula in it.

=SUMIFS(D5:D13, C5:C13,">="&TODAY()-60, C5:C13,"<="&TODAY())

  • Next, press Enter.
  • Consequently, you will get the total quantity in cell G5.

🔎 How Does the Formula Work?

♣️ Formula: SUMIFS(D5:D13, C5:C13,”>=”&TODAY()-60, C5:C13,”<=”&TODAY())

👉 The TODAY function has no argument to pass in its parameters. This function is useful when you need to have the current date presented on a worksheet, regardless of when you open the workbook.

👉 In the formula firstly we have passed the range of our cells, which is D4:D12 then the condition range which is C4:C12. After that, we check if the criteria range is within the last 60 days from today or not. The quantities of the selected ranges will be summed up.

💡 Note: This method will count the last 60 days from the current date. Today my date is 20-sept-2022 so here all the calculations will be based on my current date. Your total quantity will differ if you download the workbook the day after today.

Read More: How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)


Similar Readings


4. Utilizing SUMIFS Function with Excluding Blank Cells

There may occasionally be some empty cells in our dataset or table. Use the ‘Not Equal to’ operator (>) as the criteria for a range if you want to eliminate rows with empty cells. Now, using our dataset, we can determine the total sales value of the laptops that are present in the table with sufficient and complete data.

Utilizing SUMIFS Function with Excluding Blank Cells

Let’s walk through the following steps to do the task.

📌 Steps:

  • First of all, select the cell you want to put the value in (cell B25).
  • Then write down the following formula in it.

=SUMIFS(F5:F16,C5:C16,D19,D5:D16,"<>",E5:E16,"<>")

  • Next, press Enter.
  • Consequently, you will get the total sales in cell B25.

Read More: Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)


5. Use of SUMIFS with Multiple OR Logic

In some cases, we may need to extract the sum for multiple criteria that cannot be handled using only the SUMIFS function. For multiple criteria, we can simply add two or more SUMIFS functions. As an example, we would like to calculate the sum of all notebooks and desktops originating in the USA and Japan.

Let’s walk through the following steps to do the task.

📌 Steps:

  • First of all, select the cell you want to put the value in (cell B25).
  • Then write down the following formula in it.

=SUMIFS(G5:G18,C5:C18,D21,E5:E18,D22)+SUMIFS(G5:G18,C5:C18,F21,E5:E18,F22)

  • Next, press Enter.
  • Consequently, you will get the total sales in cell B25.

using sumifs function with or logic to exclude multiple criteria in same column

🔎 How Does the Formula Work?

♣️ Formula: SUMIFS(G5:G18,C5:C18,D21,E5:E18,D22)+SUMIFS(G5:G18,C5:C18,F21,E5:E18,F22)

👉 The first SUMIFS function finds the rows based on criteria 1. After getting the matched rows are summing up the prices and showing the result.

👉 In the second SUMIFS function, the rows are found based on criteria 2. The results are shown after summing up the prices for the matched rows. Last but not least, the plus operator sums both results to give us the total sales.

Read More: Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria


6. Combining SUM and SUMIFS Functions

The dataset we have here contains details of an order for a company. There are four attributes in the table: Order ID, Product Names, Category, and Price.  As an example, let’s count the total prices where the category status is A and B. For the purpose of calculation, we will combine SUM and SUMIFS functions.

Exclude Multiple Criteria in Same Column Using SUM and SUMIFS Functions

Let’s walk through the following steps to do the task.

📌 Steps:

  • First of all, select the cell you want to put the value in (cell H5).
  • Then write down the following formula in it.

=SUM(SUMIFS(E5:E12,D5:D12,{"A","B"}))

  • Next, press Enter.
  • Consequently, you will get the total sales in cell H5.

🔎 How Does the Formula Work?

♣️ Formula: SUM(SUMIFS(E5:E12,D5:D12,{“A”,”B”}))

👉 The SUMIFS function finds the rows where category status is A or B. After getting the matched rows are summing up the prices and showing the result.

👉 Then all the prices will be summed up to get the total price using the SUM function.

Read More: How to Use VBA Sumifs with Multiple Criteria in Same Column


💬 Things to Remember

✎ If you input an array condition into SUMIFS and at the same time it finds a merged cell as the return destination, it will return #SPILL error.

✎ The function will return zero(0) instead of showing an error unless you use the Double-Quotes(““) outside a text value as range criteria. Therefore, you should be careful when entering text values as criteria inside the SUMIFS function.


Conclusion

That’s the end of today’s session. I strongly believe that from now, you may be able to exclude multiple criteria in the same column using the SUMIFS function in Excel. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website ExcelDemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo