The SUMIF function in Excel

The SUMIF function in Excel allows us to make a sum of cells that meet a certain criterion and thus exclude those cells that we are not interested in including in the operation.

SUMARIF function syntax

The SUMIF function it has three arguments which I will explain below.

SUMARIF function syntax in Excel

  • Rank (): The range of cells that will be evaluated.
  • Criterion (): The condition that the cells that will be included in the sum must meet.
  • Sum_range (): The cells to be added. In case it is omitted, the cells specified in Range will be added.

The The sum can be specified as a number, text, or expression. If it is a number, it will only add the cells that are equal to that number. If the criterion is an expression we can specify some condition of greater or less than.

If he is a text is because surely I need a condition to be met in a certain column that contains data of type text but to perform the sum of another column that has numeric values. All these cases will become clearer with the following examples.

Examples of the SUMIF function

The first example is very simple since from a list of random values ​​I want to add all the cells that contain the number 5.

Example of the SUMARIF function in Excel

Remember that the ADDIF function does not count the cells containing the number 5, otherwise the result would have been 2. The ADDIF function Find the cells that have the number 5 and add their value. Since cells A2 and A7 meet the established condition, the sum of both cells is made, which gives the number 10 as a result.

Now I will change the condition to an expression and add those cells that are less than 3. Observe the result of this new formula.

Criterion of the function SUMARIF as expression

Sales from a seller

Now we will use a criterion in text and the third argument of the ADDIF function which lets us specify a range of sum different from the range where the criterion is applied. In the following example I have a list of sellers and I want to know the total sales for a specific seller.

To get the result I will put the range A2: A10 as the range that must be equal to the text in cell F1. The third argument of the function contains the range C2: C20 which has the amounts that I want to add.

Ciretery of the SUMARIF function as text

Cell F2 containing the ADDIF function shows the sum of the sales that belong to Juan and excludes the rest of the cells. You could modify this formula a bit to get the sales for a specific month. Observe the result of this adaptation in cell F5:

Specify a range of sum in the SUMIF function in Excel

Leave a Comment