Sumif With Multiple Criteria In Excel How To Use

SUMIF with Multiple Criteria in Excel

How to use SUMIF with Multiple Criteria?

The SUMIF function with multiple criteria is SUMIF(range, criteria, sum_range).

The following operators are used in the criteria argument:

Comparison operatorsGreater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=)Equal to (=), not equal to (<>)Wildcard charactersAsterisk (*) [implies any number of characters]Question mark (?) [implies a single character]

Let us consider a few examples to understand SUMIF with multiple criteria.

Greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=)Equal to (=), not equal to (<>)

#1–Text Criteria (Exact Match)

The following table contains the item sales for a particular location of an organization. We want to sum the laptop sales of the organization.

#2–Text Criteria With Drop-Down List

The unfamiliar users of MS Excel usually do not use the formula given in “step 1” of “example #1.” The word “laptop” is directly specified in the “criteria” argument of the formula.

Enter the formula shown in the following image. The range B2:B22 (column “item”) is compared with the criteria “laptop.” The sum_range is D2:D22 (column “total sales amount”) Press the “Enter” key and the output appears, as shown in the following image.

The range B2:B22 (column “item”) is compared with the criteria “laptop.” The sum_range is D2:D22 (column “total sales amount”)

Alternatively, the cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more can be given. The value in the referenced cell may be keyed or selected from the drop-down list.

Working on example #1, let us create a drop-down listDrop-down ListA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more to sum laptop sales. The steps are given as follows:

Step 1: Select cell F5 which contains the word “laptop.” Delete this word.
Step 2: In the Data tab, go to the “data tools” section.
Step 3: Select “data validationData ValidationThe data validation in excel helps control the kind of input entered by a user in the worksheet.read more” from the drop-down list.
Step 4: In the “data validation” dialog box, select “list” under “allow” criteria. In “source,” type “laptop, tablet, mobile” as these are the unique products. Click “Ok.”
Step 5: The drop-down list is created, as shown in the following image.
Step 6: In the previous formula (under “step 1” of “example #1”), enter the reference of cell F5 in the “criteria” argument. Press the “Enter” key and the output appears (same as “step 2” of “example #1”).

With every change in the value of cell F5, the “total sales amount” updates automatically. The value in cell F5 is selected from the drop-down list.

If “tablet” is selected, the tablet sales amount appears in cell G5.

#3–Text Criteria (Partial Match)

The following table shows the product-wise sales of an organization. The asterisk (*) indicates the items having high profit marginsProfit MarginsProfit Margin is a metric that the management, financial analysts, & investors use to measure the profitability of a business relative to its sales. It is determined as the ratio of Generated Profit Amount to the Generated Revenue Amount. read more.

We want to sum the sales of the products which contain the word “top.”

We apply the formula shown in the following image.

The output appears as shown in the succeeding image. It calculates the sum of the “laptop,” the “desktop,” and the “laptop adapter.”

#4–Wildcard Character

Working on example #3, we want to sum the sales of the products containing the asterisk.

The asterisk (*) is a wildcard characterWildcard CharacterIn Excel, wildcards are the three special characters asterisk, question mark, and tilde. Asterisk denotes multiple characters, a question mark denotes a single character, and a tilde denotes the identification of a wild card character.read more of Excel. To find this character, we use the escape character tilde (~).

We apply the formula shown in the succeeding image.

The first and the last character of the criteria argument (*~**) is an asterisk. This suggests that the asterisk can be placed anywhere in the name of the product. It can either be the first character, the last character, or any character in-between.

Following the first asterisk is a tilde (~) sign with another asterisk (*). This implies that the formula should pick those products which contain the asterisk symbol.

The output of the formula is shown in the following image.

In the SUMIF formulaSUMIF FormulaThe SUMIF Excel function calculates the sum of a range of cells based on given criteria. The criteria can include dates, numbers, and text. For example, the formula “=SUMIF(B1:B5, “<=12”)” adds the values in the cell range B1:B5, which are less than or equal to 12. read more, the “range” and the “sum_range” argument must be of the same size. In other words, the numbers of rows and columns of both ranges must be equal.

If the condition is satisfied, the formula begins summing with the first cell (top left) of the “sum_range.” It includes the same number of rows and columns as the “range.”

Note: This caution also applies to the SUMIFS function.

Frequently Asked Questions

For every column, a SUMIF formula should be written. The multiple results are then added using the SUM function. The combined formula is given as follows: “=SUM(SUMIF(range, criteria, sum_range),SUMIF(range, criteria, sum_range))” Note: If there are more columns, more SUMIF formulae can be added to this combined formula.

The “range” and the “sum_range” arguments must always be entered as ranges. If an array (like 5,6,7,8) is entered in the SUMIF formula, it returns an error. For example, the formula “=SUMIF(A1:A3,“sunflower”,B1:B3)” is correct. The formula “=SUMIF({“rose”,”sunflower”,”lotus”},“sunflower”,B1:B3)” is incorrect.

The SUMIF with multiple criteria helps find the sum of numbers that satisfy either of the two given conditions. This is the OR logic and the formula is given as follows: “=SUMIF(range,criteria1,sum_range)+SUMIF(range,criteria2,sum_range)” The formula looks for numbers that satisfy either “criteria 1” or “criteria 2” and returns their combined sum.

Key Takeaways

The SUMIF with multiple criteria sums the cell values based on the conditions provided. The SUMIF function works with a single criterion while the SUMIFS functionSUMIFS FunctionSUMIFS is an enhanced version of the SUMIF formula in Excel that enables you to sum any range of data by matching several criteria. read more works with multiple criteria.The criteria argument of the SUMIF function is based on dates, numbers, and text.The SUMIF function works with text criterion that is entered using a drop-down list.The various comparison operators and wildcard characters (* and ?) can be used in the criteria argument of the SUMIF function.The SUMIF function works with multiple criteria when used with the OR logic.