Sum Formula In Excel

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.


Sum Formula In Excel






How To Use Sum Formula in Excel

In Excel, the Sum function adds all numbers in a range of cells and returns the result.This Excel tutorial has been created to help you learn microsoft excel online.

Syntax:

SUM(number1,[number2],…)

Argument name Description
number1    (Required) The first number you want to add. The number can be like 4, a cell reference like B6, or a cell range like B2:B8.
number2-255    (Optional) This is the second number you want to add. You can specify up to 255 numbers in this way.
Quick Sum with the Status Bar

If you want to quickly get the Sum of a range of cells, all you need to do is select the range and look in the lower right-hand side of the Excel window.



Screen shot of selecting a range of cells, then looking in the Status Bar

Status Bar

This is the Status Bar, and it displays information regarding whatever you have selected, whether it’s a single cell or multiple cells. If you right-click on the Status Bar a feature dialog box will pop out displaying all of the options you can select. Note that it also displays values for your selected range if you have those attributes checked. Learn more about the Status Bar.

Using the AutoSum Wizard

The easiest way to add a SUM formula to your worksheet is to use the AutoSum Wizard. Select an empty cell directly above or below the range that you want to sum, and on the Home or Formula tabs on the Ribbon, press AutoSum > Sum. The AutoSum Wizard will automatically sense the range to be summed and build the formula for you. It can also work horizontally if you select a cell to the left or right of the range to be summed. Note it’s not going to work on non-contiguous ranges, but we’ll go over that in the next section.

You can use the AutoSum Wizard to automatically build a Sum formula. Select a range above/beneath or left/right of the range to be summed and goto the Formula tab on the Ribbon, then select AutoSum & SUM.

Use the AutoSum Wizard to quickly sum contiguous ranges

The AutoSum dialog also lets you select other common functions like:



AutoSum vertically

Cell B6 shows the AutoSum Sum formula: =SUM(B2:B5)The AutoSum Wizard has automatically detected cells B2:B5 as the range to be summed. All you need to do is press Enter to confirm it. If you need add/exclude more cells, you can hold the Shift Key > Arrow key of your choice until your selection matches what you want, and press Enter when you’re done.

Intellisense function guide: the SUM(number1,[number2], …) floating tag beneath the function is its Intellisense guide. If you click the SUM or function name, it will turn into a blue hyperlink, which will take you to the Help topic for that function. If you click the individual function elements, their representative pieces in the formula will be highlighted. In this case only B2:B5 would be highlighted since there is only one number reference in this formula. The Intellisense tag will appear for any function.

AutoSum horizontally

Cell D2 shows the AutoSum Sum formula: =SUM(B2:C2)

Using SUM with non-contiguous cells

Using SUM with non-contiguous ranges. Cell C8's formula is =SUM(C2:C3,C5:C6). You could also use Named Ranges, so the formula would be =SUM(Week1,Week2).The AutoSum Wizard will generally only work for contiguous ranges, so if you have blank rows or columns in your sum range, Excel is going to stop at the first gap. In that case you’d need to SUM by selection, where you add the individual ranges one by one. In this example if you had data in cell B4, Excel would generate =SUM(C2:C6) since it would recognize a contiguous range.



You can quickly select multiple, non-contiguous ranges with Ctrl+Left Click. First, enter “=SUM(“, then select your different ranges and Excel will automatically add the comma separator between ranges for you. Press enter when you’re done.

TIP: you can use ALT+ = to quickly add the SUM function to a cell. Then all you need to do is select your range(s).

Note: you may notice how Excel has highlighted the different function ranges by color, and they match within the formula itself, so C2:C3 is one color, and C5:C6 is another. Excel will do this for all functions, unless the referenced range is on a different worksheet or in a different workbook. For enhanced accessibility with assistive technology, you can use Named Ranges, like “Week1”, “Week2”, etc. and then reference them in your formula:

=SUM(Week1,Week2)

How to add, subtract, multiply and divide with Excel

You can easily perform mathematical operations with Excel on their own, and in conjunction with Excel functions like SUM. The following table lists the operators that you can use, along with some related functions. You can input the operators from either the number row on your keyboard, or the 10-key pad if you have one. For instance, Shift+8 will enter the asterisk (*) for multiplication.

Operator Operation Examples
+ Addition =1+1

=A1+B1

=SUM(A1:A10)+10

=SUM(A1:A10)+B1

Subtraction =1-1

=A1-B1

=SUM(A1:A10)-10

=SUM(A1:A10)-B1

* Multiplication =1*1

=A1*B1

=SUM(A1:A10)*10

=SUM(A1:A10)*B1

=PRODUCT(1,1) – PRODUCT function

/ Division =1/1

=A1/B1

=SUM(A1:A10)/10

=SUM(A1:A10)/B1

=QUOTIENT(1,1) – QUOTIENT function

^ Exponentiation =1^1

=A1^B1

=SUM(A1:A10)^10

=SUM(A1:A10)^B1

=POWER(1,1) – POWER function

For more information, see Use Excel as your calculator.

Other Examples

  1. Let’s say you want to apply a Percentage Discount to a range of cells that you’ve summed.Using Operators with SUM. Formula in cell B16 is =SUM(A2:A14)*-25%. The formula would be constructed properly if -25% was a cell reference instead, like =SUM(A2:A14)*E2
    • =SUM(A2:A14)*-25%

    Would give you 25% of the summed range, however that hard-codes the 25% in the formula, and it might be hard to find later if you need to change it. You’re much better off putting the 25% in a cell and referencing that instead, where it’s out in the open and easily changed, like this:

    • =SUM(A2:A14)*E2

    To divide instead of multiply you simply replace the “*” with “/”: =SUM(A2:A14)/E2

  2. Adding or Subtracting from a SUMi. You can easily Add or Subtract from a Sum using + or – like this:
    • =SUM(A1:A10)+E2
    • =SUM(A1:A10)-E2
Best Practices with SUM

This section will discuss some best practices for working with the SUM function. Much of this can be applied to working with other functions as well.

The =1+2 or =A+B Method – While you can enter =1+2+3 or =A1+B1+C2 and get fully accurate results, these methods are error prone for several reasons:

  1. Typos – Imagine trying to enter more and/or much larger values like this:
    • =14598.93+65437.90+78496.23

    Then try to validate that your entries are correct. It’s much easier to put these values in individual cells and use a SUM formula. In addition, you can format the values when they’re in cells, making them much more readable then when they’re in a formula.

    Use the SUM function instead of hard-coding values in formulas. Formula in cell D5 is =SUM(D2:D4)

  2. #VALUE! errors from referencing text instead of numbersIf you use a formula like:
    • =A1+B1+C1 or =A1+A2+A3

    Example of poor formula construction. Formula in cell D2 is =A2+B2+C2Your formula can break if there are any non-numeric (text) values in the referenced cells, which will return a #VALUE! error. SUM will ignore text values and give you the sum of just the numeric values.

    Proper formula construction. Instead of =A2+B2+C2, cell D2's formula is =SUM(A2:C2)

  3. #REF! error from deleting rows or columns#REF! error caused by deleting a column. Formula has changed to =A2+#REF!+B2If you delete a row or column, the formula will not update to exclude the deleted row and it will return a #REF! error, where a SUM function will automatically update.

    SUM function will automatically adjust for inserted or deleted rows and columns

  4. Formulas won’t update references when inserting rows or columns=A+B+C formulas won't update if you add rowsIf you insert a row or column, the formula will not update to include the added row, where a SUM function will automatically update (as long as you’re not outside of the range referenced in the formula). This is especially important if you expect your formula to update and it doesn’t, as it will leave you with incomplete results that you might not catch.

    Example portrays a SUM formula automatically expanding from =SUM(A2:C2) to =SUM(A2:D2) when a column was inserted

  5. SUM with individual Cell References vs. RangesUsing a formula like:
    • =SUM(A1,A2,A3,B1,B2,B3)

    Is equally error prone when inserting or deleting rows within the referenced range for the same reasons. It’s much better to use individual ranges, like:

    • =SUM(A1:A3,B1:B3)

    Which will update when adding or deleting rows.

 

On http://www.myelesson.org you get excel help and online file download facitity. This video lesson covers the entire topic in simple and easy to understand step by step approach.
Hindi








Our Other Services




Read Free Ebooks

myeboook.com

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!



Buy Excel Course

myelesson.org/product

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788



Create Best Looking Resume

http://www.makecv.org/

At Makecv.org you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!


Download Excel Crash Course


This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.



Learn Photoshop Web Designing


You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .



Learn HTML CSS


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .


Download Full list of Excel Shortcuts


This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.




Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on www.Myelesson.org




Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from myelesson.org .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.




List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.





Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok
Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok