SUMIF with Multiple Criteria

The SUMIF (SUM+IF) with multiple criteria sums the cell values based on the conditions provided. The criteria are based on dates, numbers, and text. The SUMIF function works with a single criterion, while the SUMIFS function works with multiple criteria in excel.

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

The following operators are used in the criteria argument:

• Comparison operators
  • Greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=)
  • Equal to (=), not equal to (<>)
• Wildcard characters
• Asterisk (*)
• Question mark (?)

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

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

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.

Alternatively, the cell reference 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 list 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 validation” 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.

The following table shows the product-wise sales of an organization. The asterisk (*) indicates the items having high profit margins.

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.”

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

The asterisk (*) is a wildcard character 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.

The Caution While Specifying Range

In the SUMIF formula, 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.