How To Find The Sum In Excel

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 More...Less

The SUM role adds values. Y'all can add private values, cell references or ranges or a mix of all three.

For example:

=SUM(A2:A10) Adds the values in cells A2:10.
=SUM(A2:A10, C2:C10) Adds the values in cells A2:10, every bit well every bit cells C2:C10.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

SUM(number1,[number2],...)

Statement proper name	Description
number1 Required	The offset number y'all want to add. The number tin exist like four, a cell reference similar B6, or a jail 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.

Statement proper name

Description

number1

Required

The offset number y'all want to add. The number tin exist like four, a cell reference similar B6, or a jail 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.

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 =ane+ii or =A+B Method – While yous can enter =1+2+iii or =A1+B1+C2 and get fully accurate results, these methods are error prone for several reasons:

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 right. It'south 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 so when they're in a formula.
#VALUE! errors from referencing text instead of numbers

If you lot utilise a formula like:
- =A1+B1+C1 or =A1+A2+A3
Your formula tin can suspension if there are any not-numeric (text) values in the referenced cells, which volition return a #VALUE! error. SUM will ignore text values and give you lot the sum of merely the numeric values.
#REF! error from deleting rows or columns

If you delete a row or column, the formula will not update to exclude the deleted row and it will render a #REF! error, where a SUM function volition automatically update.
Formulas won't update references when inserting rows or columns

If you insert a row or cavalcade, the formula will non update to include the added row, where a SUM function will automatically update (every bit long equally you're not outside of the range referenced in the formula). This is especially of import if you expect your formula to update and it doesn't, as it will get out you with incomplete results that you lot might not catch.
SUM with private Cell References vs. Ranges

Using a formula similar:
- =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 improve to utilise private ranges, like:
- =SUM(A1:A3,B1:B3)
Which will update when adding or deleting rows.

I but desire to Add/Subtract/Multiply/Dissever numbers Come across this video series on Basic Math in Excel, or Employ Excel as your figurer.
How exercise I show more/less decimal places? Yous can change your number format. Select the cell or range in question and use Ctrl+one to bring upwardly the Format Cells Dialog, so click the Number tab and select the format you desire, making certain to point the number of decimal places you want.
How exercise I add or subtract Times? You can add and decrease times in a few different means. For example, to get the difference between 8:00 AM - 12:00 PM for payroll purposes you would use: =("12:00 PM"-"viii:00 AM")*24, taking the end fourth dimension minus the start time. Annotation that Excel calculates times as a fraction of a day, so yous need to multiply by 24 to go the total hours. In the kickoff example we're using =((B2-A2)+(D2-C2))*24 to get the sum of hours from first to finish, less a tiffin break (8.50 hours full).

If you lot're only calculation hours and minutes and desire to display that fashion, then you can sum and don't need to multiply by 24, so in the second example we're using =SUM(A6:C6) since we but need the total number of hours and minutes for assigned tasks (five:36, or v hours, 36 minutes).

For more data, see: Add or decrease time.
How exercise I get the difference between dates? As with times, you tin can add together and decrease dates. Hither's a very common example of counting the number of days between ii dates. It's as simple as =B2-A2. The key to working with both Dates and Times is that you lot commencement with the Cease Date/Time and subtract the Get-go Date/Fourth dimension.

For more than ways to work with dates see: Calculate the departure between two dates.
How do I sum only visible cells? Sometimes, when you manually hibernate rows or apply AutoFilter to display but certain data you also but want to sum the visible cells. You can apply the SUBTOTAL function. If you're using a total row in an Excel table, any role you lot select from the Total drop-down will automatically exist entered as a subtotal. Run into more about how to Total the data in an Excel tabular array.