articles

Home / DeveloperSection / Articles / Sum Function in Excel

Sum Function in Excel

AVADHESH PATEL6226 23-Mar-2013

This article I have explain how to used sum function, when working with excel. For example calculate student mark, product total price or total quantity. Sum formula used with many condition that described one by one below with example.

Sum

First create your excelling sheet and insert some dummy records as below.

Figure 1:

Sum Function in Excel

Now, Select cell where you see populated value then go to formula bar and used below formula for sum and press enter button.

=SUM (B2:B6)

Figure 2:

Sum Function in Excel

See out put

Figure 3:

Sum Function in Excel

Same formula you can apply for total. For example, if you want to calculate some of multiple columns or multiple row value, then simple pass cell index range. For example if I want get total value of “Marks” and “Max” column then formula as given below

=SUM (B2:C6) 

Sum a range of cells OFFSET

If you insert a row directly above the SUM function in the previous example, the new row may not be included in the SUM. It may continue to sum cells B2:B6, and ignore B7. To ensure that new rows are included in the total, you can use the OFFSET function with the SUM function.

Formula is given below

=SUM (B2:OFFSET(B7,-1,0))

Now insert one row and see output

Figure 4:

Sum Function in Excel

You have seen, when new row added into sheet, that value auto calculated into in to “Total” row.

Sum cells that match criteria – SUMIF

You can calculate a total for rows that meet a specific criterion. In this example only the rows with Laptop orders will be included in the total. Below I have given formula and prefer to write don’t copy past.

=SUMIF(A2:A9,"Laptop",B2:B9)

Figure 5:

Sum Function in Excel

  Match criterion in a string

You can add cells that contain a criterion as part of the cell's contents. For example all Laptop, Dell Laptop, and HCL Laptop etc. orders will be summed, because they contain the string "Laptop".

=SUMIF(A2:A9,"*Laptop*",B2:B9)

Figure 6:

Sum Function in Excel

Criterion and operator

You can use an operator with a criterion. In this example only the rows where the number of sales reps is greater than or equal to ten will be included in the total.

=SUMIF(B2:B9,">=10",C2:C9)

Figure 7:

Sum Function in Excel

 Sum cells that match multiple criteria - SUMIFS 

In Excel 2007 and later versions, you can use the SUMIFS function to calculate a total for rows that meet two or more criteria. In this example only the row where the Device is "Laptop" and the number of order is greater than or equal to ten will be included in the min order.

=SUMIFS(B2:B9,A2:A9,"*LAPTOP*",C2:C9,">=10")

Figure 8:

Sum Function in Excel

Sum of Few Top Numbers

If a few numbers are to be summed, e.g. top 3, you can type the numbers into the formula. For example:

=SUM(LARGE(C2:C11,{1,2,3}))

Figure 9:

Sum Function in Excel

Sum of Many Top Numbers

If many top numbers are to be summed you can include the INDIRECT function in the formula with the SUM function. In the INDIRECT function, use row numbers that represent the numbers you want to include. In this example, rows 1:10 are used, so the top 5 numbers in the referenced range will be summed.

=SUM(LARGE(C2:C11,ROW(INDIRECT("1:5"))))

Note: This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter

Figure 10:

Sum Function in Excel

Sum Variable Top Numbers

If a variable number of top numbers are to be summed you can include the INDIRECT function in the formula with the SUM function, as shown above, and refer to a cell that holds the variable..

In cell d2, type the number of top cells, e.g. 5

=SUM(LARGE(C2:C11,ROW(INDIRECT("1:"&D2))))

This is an array formula, and must be array-entered. To do this, hold the Ctrl and Shift keys, and press Enter

Figure 11: 

Sum Function in Excel


Updated 07-Sep-2019
Avadhesh Kumar Patel District Project Manager - Aligarh 14 months work experience in Panchayati Raj Department Sector as District Project Manager & 12 months work experience in IT Sector as Software Engineer. :-)

Leave Comment

Comments

Liked By