Method 1 – Excluding Multiple Criteria
We have a dataset of some fruits with their prices. We will find the total cost of all the fruits except Oranges and Bananas.
Steps:
- Select the cell you want to put the value in (cell F5).
- Insert the following formula.
=SUMIFS(C5:C13,B5:B13,"<>Oranges",B5:B13,"<>Bananas")
- Press Enter.
Read More: How to Use SUMIFS with Multiple Criteria in the Same Column
Method 2 – Excluding Multiple Criteria Based on Wildcard Characters
We need the total costs of some fruits that have names which end in “Apples” and “Lemons”.
Steps:
- Select the cell you want to put the value in (cell F5).
- Insert the following formula.
=SUM(SUMIFS(C5:C13,B5:B13,{"*Apples","*Lemons"}))
- Press Enter.
How Does the Formula Work?
♣️ Formula: SUM(SUMIFS(C5:C13,B5:B13,{“*Apples”,”*Lemons”}))
The asterisk at the beginning of “*Apples” and “*Lemons” will help the function find all fruit names which contain these words at the end.
Read More: How to Use SUMIFS Function with Wildcard in Excel
Method 3 – Using SUMIFS with Multiple Dates as Criteria
We have a dataset of some fruits with the delivery date and quantity. We’ll calculate the total number of deliveries made over the previous 60 days.
Steps:
- Select the cell you want to put the value in (cell G5).
- Insert the following formula in it.
=SUMIFS(D5:D13, C5:C13,">="&TODAY()-60, C5:C13,"<="&TODAY())
- Press Enter.
How Does the Formula Work?
♣️ Formula: SUMIFS(D5:D13, C5:C13,”>=”&TODAY()-60, C5:C13,”<=”&TODAY())
The TODAY function fetches the current date whenever you open or recalculate the workbook.
The formula checks whether each date in the condition range, C4:C12, is within the last 60 days from today. For each date that passes the check, the value of the accompanying cell in the D column will be added to the sum.
Note: This method will count the last 60 days from the current date. Your total quantity will differ if you use the sample as-is.
Read More: How to Apply SUMIFS with Multiple Criteria in Different Columns
Method 4 – Utilizing the SUMIFS Function while Excluding Blank Cells
Using our dataset, we can determine the total sales value of the laptops that are present in the table with sufficient and complete data.
Steps:
- Select the cell you want to put the value in (cell B25).
- Use the following formula in it.
=SUMIFS(F5:F16,C5:C16,D19,D5:D16,"<>",E5:E16,"<>")
- Press Enter.
Method 5 – Use of SUMIFS with Multiple OR Logic
For multiple criteria, we can simply add two or more SUMIFS functions. We will calculate the sum of all notebooks and desktops originating from the U.S. and Japan.
Steps:
- Select the cell you want to put the value in (cell B25).
- Use the following formula in it.
=SUMIFS(G5:G18,C5:C18,D21,E5:E18,D22)+SUMIFS(G5:G18,C5:C18,F21,E5:E18,F22)
- Press Enter.
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.
In the second SUMIFS function, the rows are found based on criteria 2.
The plus operator sums both results to give the total sales.
Read More: SUMIFS with Multiple Criteria Along Column and Row in Excel
Method 6 – Combining the SUM and SUMIFS Functions
The dataset we have here contains details of company orders. There are four attributes in the table: Order ID, Product Names, Category, and Price. Let’s count the total prices where the category status is A and B.
Steps:
- Select the cell you want to put the value in (cell H5).
- Use the following formula in it.
=SUM(SUMIFS(E5:E12,D5:D12,{"A","B"}))
- Press Enter.
How Does the Formula Work?
♣️ Formula: SUM(SUMIFS(E5:E12,D5:D12,{“A”,”B”}))
The SUMIFS function finds the rows where the category status is A or B, then sums the values from the E column for each match.
Read More: SUMIFS: Sum Range Across Multiple Columns
Things to Remember
✎ If you input an array condition into SUMIFS and it finds a merged cell as the return destination, it will return a #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. Be careful when entering text values as criteria inside the SUMIFS function.
Download the Practice Workbook
Related Articles
- Excel SUMIFS with Multiple Vertical and Horizontal Criteria
- How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows
- SUMIFS: Sum Range Across Multiple Columns
- How to Use VBA SUMIFS with Multiple Criteria in Same Column
<< Go Back to Excel SUMIFS with Multiple Criteria | Excel SUMIFS Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
