Exclude Multiple Criteria in Same Column with SUMIFS Function

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.

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.

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.

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.

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.

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

Exclude Multiple Criteria.xlsx