SUM,SUMIF,SUMIFS

SUM function in Excel

To sum a range of cell sum function is used.

SUM Function Syntax:

=SUM(number 1,number 2,number 3,….number n)

SUM Function with Example:

To do the sum of the total item sold in Q1 SUM formula is used in Cell C11.  We need to select the range of cell required.

We can quickly do the sum with use of Autosum command from the command bar.

  1. Select the blank cell in the row below the cell that we want to sum (cell C11 in below example).

2. Click on AutoSum command from Formula Tab.

3. Sum formula will appear in the active cell with reference to the cells above.

4. Press the enter key to complete the entry.

SUMIF FUNCTION IN EXCEL

SUMIF function is used to sum the values in the range that meet one specified criteria.

Syntax of SUMIF: =SUMIF(range, criteria, [sum_rabge])

Argument:

range- The range of cell against which you want to apply criteria. Only cell with numbers are summed.

Criteria– Need to define criteria. It must be a specific criteria.

Sum_range–  It is the range of the cells required to do the sum for given single criteria.

SUMIF Function with the example:

Let us calculate the number of Shirt, T-shirt and Jacket sold at the end of the first quarter.

With help of SUMIF we can find out Total shirt sold Q1, Total T-Shirt sold Q1 & Total Jacket sold Q1.

In above example range of criteria is B2: B10.

Criteria are “Shirt” in B11, “T-shirt” in B12 & “Jacket” in B13.

Sum range is C2: C10.

SUMIFS FUNCTION IN EXCEL

Use of SUMIFS Function in excel

The SUMIFS function is used to find a conditional sum of values of specified range or array based on multiple criteria.

Syntax: 

SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],  …)

Arguments:

sum_range : one or more cell or range of cell or array to do sum. Only cell with numbers are summed.

Criteria_range1: First range to be evaluated by associated criteria.

Criteria1: The first condition that must be met or required. Criteria can be in the form of number, text, logical expression, cell reference or another excel function. Criterial1 is applied against Criteria_range1.

Criteria_range2: (Optional) It is the range of cells that we want to apply criteria2.

Criteria2: It second condition that must be met or required. Criteria can be in the form of number, text, logical expression, cell reference or another excel function. Criterial2 is applied against Criteria_range2.

EXAMPLE OF SUMIFS FUNCTION IN EXCEL

Let’s calculate Total number of Shirt sold in Q1 that of Red color.

Here we want a sum of shirt sold in Q1 with two criteria. Criteria1: sold item is Shirt. Criteria2: Sold item is of red color.

Recommended For You

About the Author: Mehul Vaghela

Howdy Math fan. I'm Mehul Vaghela - B.E.Mechanical. Enthusiastic and holds the passion of reading, writing, and learning. In 10 years of experience on the field, I've faced so much problem with managing data and learned to make that happen. ExcelElixir is one of the parts of passing it on (knowledge).

2 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *