--- title: "Sum Function in Excel" description: "This article I have explain how to used sum function, when working with excel. For example calculate student mark, product total price or total quanti" author: "AVADHESH PATEL" published: 2013-03-23 updated: 2019-09-07 canonical: https://www.mindstick.com/articles/1162/sum-function-in-excel category: "excel" tags: ["excel"] reading_time: 4 minutes --- # Sum Function in Excel This [article](https://yourviews.mindstick.com/view/81489/kashmir-now-after-an-year-of-abrogation-of-article-370) I have [explain](https://www.mindstick.com/forum/159699/what-is-a-compilation-error-in-dot-net-core-explain-with-an-example) how to used sum [function](https://www.mindstick.com/articles/43970/purchase-suitable-wedding-dresses-for-your-wedding-function), when working with excel. For example calculate [student](https://yourviews.mindstick.com/view/84686/importance-of-brahmacharya-in-student-life) mark, [product](https://yourviews.mindstick.com/view/83909/why-mindstick-platform-is-so-important-for-our-product-promotions) total price or total quantity. Sum [formula](https://yourviews.mindstick.com/view/81488/usa-needs-masks-for-all-formula-in-corona-pandemic) used with many [condition](https://www.mindstick.com/forum/12711/select-query-with-and-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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/f01b50f1-ead1-4539-aaf9-1abc2b9da5e4.png) 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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/623e1570-42e0-4b6d-8d7b-1adc6edc039d.png) See out put ##### Figure 3: ![Sum Function in Excel](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/7413f037-a63b-46d7-9339-210a65ebd70e.png) 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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/4b5f0cb1-3f3b-4ce9-9e78-c2348f6b3b5d.png) You have seen, when new row added into sheet, that value auto [calculated](https://answers.mindstick.com/qa/31699/what-is-sensex-and-how-it-is-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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/030fb52c-21d1-4103-baa3-8fdbe5be8b0f.png) 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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/4370501a-5037-4945-ae23-3e119920c6bc.png) ##### Criterion and operator You can use an [operator](https://www.mindstick.com/blog/144/union-intersection-and-except-operator-in-sql-server) 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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/6456a078-709e-4f41-829c-8b4da1a19da0.png) ##### 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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/74ce9575-fe78-481a-8e84-894b64080217.png) ##### 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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/de531562-6ada-42ce-a0d0-f5cfbee72ed0.png) ##### 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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/ba537ad9-91b8-4845-8703-74cad7621844.png) ##### Sum Variable Top Numbers If a [variable](https://www.mindstick.com/blog/11493/what-is-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](https://www.mindstick.com/mindstickarticle/e790a001-fcca-4077-bdbe-66da92bcc5fb/images/a3d1ff98-fa7d-4c66-82f5-bdadbc0e5c4d.png) --- Original Source: https://www.mindstick.com/articles/1162/sum-function-in-excel Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.