how to solve sum problem in excel

How to correct a #VALUE! error in AVERAGE or SUM functions

If AVERAGE or SUM refer to cells that contain #VALUE! errors, the formulas will result in a #VALUE! error.

In order to overlook the error values, we’ll construct a formula that ignores the errors in the reference range while calculating the average with the remaining “normal” values.

To work around this scenario, we use a combination of AVERAGE along with IF and ISERROR to determine if there is an error in the specified range. This particular scenario requires an array formula:

=AVERAGE(IF(ISERROR(B2:D2),"",B2:D2))

This is an array formula. So you either need to press ENTER (only if you are using Microsoft 365) or CTRL+SHIFT+ENTER in the formula.

Note: If you have a current version of Microsoft 365 , then you can simply enter the formula in the output cell, then press ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output cell, entering the formula in the output cell, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see Guidelines and examples of array formulas .

Array function in AVERAGE to resolve the #VALUE! error

Note: The above function will not only work for #VALUE!, but also for #N/A, #NULL, #DIV/0!, and others.

You could also use SUM in the same fashion:

=SUM(IF(ISERROR(B2:D2),"",B2:D2))

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community .

Correct a #VALUE! error

AVERAGE function

SUM function

Overview of formulas in Excel

Detect errors in formulas

All Excel functions (alphabetical)

All Excel functions (by category)

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

Ablebits blog

How to use AutoSum in Excel

This short tutorial explains what AutoSum is and shows the most efficient ways to use AutoSum in Excel. You will see how to automatically sum columns or rows with the Sum shortcut, sum only visible cells, total a selected range vertically and horizontally in one go, and learn the most common reason for Excel AutoSum not working.

Did you know that Excel SUM is the function that people read about most? To make sure, just check out Microsoft's list of 10 most popular Excel functions . No wonder they decided to add a special button to the Excel ribbon that inserts the SUM function automatically. So, if you wanted to know "What is AutoSum in Excel?" you already got the answer :)

In essence, Excel AutoSum automatically enters a formula to sum numbers in your worksheet. For more details, check out the following sections of this tutorial.

Excel AutoSum tips

Excel autosum not working, where is the autosum button in excel.

The AutoSum button is available in 2 locations on the Excel ribbon.

How to AutoSum in Excel

Whenever you need to sum a single range of cells, whether a column, row or several adjacent columns or rows, you can have Excel AutoSum to automatically make an appropriate SUM formula for you.

To use AutoSum in Excel, just follow these 3 easy steps:

Select a cell next to the numbers you want to sum.

A Sum formula appears in the selected cell, and a range of cells you're adding gets highlighted (B2:B6 in this example):

Click the AutoSum button on either the Home or Formulas tab.

In most cases, Excel selects the correct range to total. In a rare case when a wrong range is selected, you can correct it manually by typing the desired range in the formula or by dragging the cursor through the cells you want to sum.

Press the Enter key to complete the formula.

Press the Enter key to complete the Sum formula.

Shortcut for Sum in Excel

Pressing the Equal Sign key while holding the Alt key inserts a Sum formula in a selected cells(s) exactly like pressing the AutoSum button on the ribbon does, and then you hit the Enter key to complete the formula.

How to use AutoSum with other functions

Apart from adding cells, you can use Excel's AutoSum button to insert other functions, such as:

All you need to do is select a cell where you want to insert a formula, click the AutoSum drop-down arrow, and choose the desired function from the list.

Using Excel AutoSum to automatically insert formulas other than SUM

If you select More Functions from the AutoSum drop-down list, Microsoft Excel will open the Insert Function dialog box, like it does when you click the Insert Function button on the Formulas tab, or the fx button on the Formula bar.

How to AutoSum only visible (filtered) cells in Excel

You already know how to use AutoSum in Excel to total a column or row. But did you know that you can use it to sum only visible cells in a filtered list?

If your data in organized in an Excel table (which can be easily done by pressing Ctrl + T shortcut), clicking the AutoSum button inserts the SUBTOTAL function that adds only visible cells.

Use Excel AutoSum to sum only visible cells.

For more detailed explanation of the SUBTOTAL function arguments, please see How to sum filtered cells in Excel .

How that you know how to use AutoSum in Excel to automatically add cells, you may want to learn a couple of time-saving tricks that could make your work even more efficient.

How to use AutoSum on more than one cell at a time

If you want to sum values in several columns or rows, select all the cells where you want to insert the Sum formula, and then click the AutoSum button on the ribbon or press the Excel Sum shortcut .

Using AutoSum on more than one cell at a time

How to sum selected cells vertically and horizontally

Total the selected cells vertically column-by-column.

How to copy an AutoSum formula to other cells

Once AutoSum has added a SUM (or other) function in the selected cell, the inserted formula behaves like a normal Excel formula. Consequently, you can copy that formula to other cells in the usual way, for example by dragging the fill handle. For more information, please see How to copy a formula in Excel .

Just keep in mind that Excel's AutoSum uses relative cell references (without $) that adjust to the new formula location based on the relative position of rows and columns.

For example, you can have AutoSum to insert the following formula in cell A10 to total the values in column A: =SUM(A1:A9) . And when you copy that formula to cell B10, it will turn into =SUM(B1:B9) and total the numbers in column B.

In most cases, it is exactly what you need. But if you want to copy the formula to another cell without changing the cell references, you'd need to fix the references by adding the $ sign. Please see Why use $ in Excel formulas for full details.

The most common reason for AutoSum not working in Excel is numbers formatted as text . At first sight, those values may look like normal numbers, but Excel regards them as text strings and does not include in calculations.

Select cells with numbers formatted as text, click the warning sign, and then click Convert to Number.

Numbers can become formatted as text because of various reasons, such as importing a dataset from an external source, or enclosing numeric values in double quotes in your Excel formulas. If the latter, neither green triangles nor the warning sign will appear in cells, because Excel assumes you want to output a text string on purpose.

For example, the following IF formula appears to be working fine:

=IF(A1="OK", "1", "0")

But the returned 1's and 0's are text values, not numbers! And therefore, when you try to do AutoSum on cells containing such formulas, you will always get '0' as the result.

As soon as you remove "" surrounding 1 and 0 in the above formula, Excel AutoSum will treat the outputs as numbers and they will be added up correctly.

If text-numbers are not the case, you can learn about other possible reasons in this tutorial: Excel SUM not working - reasons and solutions .

Well, this is how you do AutoSum in Excel. And if someone ever asks you "What does AutoSum do?", you can refer them to this tutorial :)

Apart from the common SUM function, did you know that Excel has a couple of other functions to conditionally sum cells? If you are curious to learn them, check out the resources at the end of this page. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

Table of contents

Overview: 7 Ways to Sum in Excel

Sum a simple range of cells

Fix numbers that don't add up

Grand Total range of cells

Running Total

Sum a range of cells -- OFFSET

Sum cells that match one criterion

Sum cells that match multiple criteria

Formulas With Table References

Sum With Multiple AND/OR Criteria

Sum cells with complex multiple criteria

Sum the Top 5 Numbers in List

Sum Amounts in a Date Range

Sum Filtered List With SUBTOTAL

Sum Filtered List With AGGREGATE

Sum Specific Items in Filtered List

Get the sample workbook

Excel Sum Not Working? Here’s How to Fix It

Excel has made performing data calculations easier than ever! However, many things could go wrong with the application’s abundant syntax. Many users have reported certain formulas, including SUM not working on MS Excel. Some have reported the value changing to 0 when running the formula or having met with an error message.

Did you just relate to the situation we just mentioned? If you unfortunately have, don’t worry; we’ll help you solve it. Keep reading this article as we list why SUM may not work and how you can fix it!

Table of Contents

Why is Excel Sum Not Working?

The SUM function may not work on your Excel for a list of reasons. You could’ve made a typing error while entering the formula or used an incorrect format. Before hopping on to the solutions, you must understand the problem you’re dealing with.

How to Fix the SUM Function in Excel?

You can easily fix a malfunctioning function in Excel on your own. After you’ve looked through the problems, you can move on to the solutions that sound more relevant to your problem. From each of the causes mentioned above, find the solutions below.

Look for Typos

Be sure you’ve written the function correctly. Additionally, see if you’ve used the correct symbols, such as the equals sign and parentheses. To call any function, you must use the equals sign first. Similarly, the parentheses sign is necessary before specifying the range of cells you want to add up.

Change Format

You’ll need to change the format of the selected entity to preferably Number to be able to perform any calculations on it. You can change the format of data from the Home ribbon on Excel.

Follow these steps to change the format of your data to Number on Excel:

Replace Unsupported Symbol

MS Excel uses the “ . ” symbol as a decimal separator by default. Excel won’t calculate the data if you use other symbols such as a comma ( , ) as a decimal separator. This is also true if you’ve any symbol except the comma symbol for the thousands divider.

If you have used these symbols incorrectly, find and replace the comma ( , ) symbol with the period ( . ) symbol and vice-versa.

Here are the steps to use the Find and Replace tool on Microsoft Excel:

Select Replace All .

Disable Show Formulas

To view the calculation results, you must turn off the Show Formulas option. You can disable this feature from the Formulas tab on the menu bar.

Follow these steps to disable the Show formula feature on MS Excel:

Launch MS Excel to open your workbook.

Locate the Formula Auditing section.

Enable Automatic Calculation .

If you want Excel to automatically re-calculate the data you’ve changed, you need to set the calculation option to Automatic . You can find the calculation on the Formulas tab on the menu bar.

Here are the steps you can refer to enable automatic calculation on MS Excel:

Repair the Office App (Windows)

You may have to repair the application for Microsoft Office. When you repair an application, your system scans for corrupted files and replaces them with new ones. As you cannot repair the Excel application individually, you’ll have to repair the entire Office application.

Follow these steps to repair the office app on your device:

Asmi Dhakal is a Tech Writer specializing in Microsoft Office-based programs at TechNewsToday. She aims to use her experience to help others learn their way around its programs. Asmi’s interest in Office products grew when she first entered the marketing industry. While she initially struggled to understand their application, she eventually understood her way around them. In her articles, she exhibits a strong understanding of related issues and attempts to push only relevant fixes. Over time, she has grown a particular fondness for MS Excel and spends her free time learning Visual Basic Analysis (VBA). She’s always on the lookout for productivity tools that can get the job done quicker in Excel. Her approach to related problems is very humane, helping new users not only fix the issue but learn more about its nature. Asmi also heavily indulges in personal writing. She is a disciplined journal writer and has maintained a regular journal for 8 years! When her schedule allows, she works to rehabilitate street animals. You can contact her at [email protected]

How to Put Check Mark in Excel

Best 52 Excel Shortcuts Only Experts Know

How to Find and Edit Hyperlinks on Excel

How to Find Unique Values in Excel

How to Find and Edit External Links in Excel

Reference Isn’t Valid Excel Error – 6 Ways to Fix it

The best solution for sum issues, after plenty searching and trying on the web. Simple and straight to the point ! THX

Glad it helped you, Joe!

Excel SUM Formula Not Adding Correctly: How to Fix

Need help with sum formula issues you're in the right place ✅.

As a spreadsheet application, Excel is one big calculator that also displays numeric data in tables and graphs. The vast majority of Excel users will need to utilize the application’s SUM function (or formula). As the SUM function adds numbers up, it’s probably Excel’s most widely utilized formula.

The SUM function will never miscalculate numbers. However, SUM function cells don’t always display the expected results.

Sometimes they might display error messages instead of numeric values. In other cases, SUM formula cells might just return a zero value. These are a few ways you can fix an Excel spreadsheet not adding up correctly.

Why is the Excel SUM formula not adding correctly?

There are several reasons why the SUM formula in Excel might not be adding correctly. Some possible causes include:

1. Check the syntax of the SUM function

2. delete spaces from the sum function, 3. widen the formula’s column, 4. remove text formatting from cells, 5. select the add and values paste special options.

First, check you’ve entered the SUM function in the formula bar with the right syntax.

The syntax for the SUM function is:

=SUM(number 1, number 2)

Number 1 and number 2 can be a cell range, so a SUM function that adds the first 10 cells of column A will look like this: =SUM(A1:A10). Make sure there are no typos of any description in that function that will cause its cell to display a #Name? error as shown directly below.

#NAME? cell error excel spreadsheet not adding up correctly

Expert tip:

Some PC issues are hard to tackle, especially when it comes to corrupted repositories or missing Windows files. If you are having troubles fixing an error, your system may be partially broken. We recommend installing Restoro, a tool that will scan your machine and identify what the fault is. Click here to download and start repairing.

The SUM function does not add up any values when there are spaces in its formula. Instead, it will display the formula in its cell.

To fix that, select the formula’s cell; and then click in the far left of the function bar. Press the Backspace key to remove any spaces before the ‘=’ at the beginning of the function. Make sure there aren’t spaces anywhere else in the formula.

Function with spaces in it excel spreadsheet not adding up correctly

If the SUM formula cell displays #######, the value might not fit within the cell. Thus, you might need to widen the cell’s column to ensure the whole number fits.

To do that, move the cursor to the left or right side of the SUM cell’s column reference box so that the cursor changes to a double arrow as in the snapshot directly below. Then hold the left mouse button and drag the column left or right to expand it.

Narrow spreadsheet column excel spreadsheet not adding up correctly

The Excel SUM function will not add up any values that are in cells with text formatting, which display text numbers on the left of the cell instead of the right side. To ensure all cells within a SUM formula’s cell range have general formatting. To fix this, follow these steps.

Select all the cells within its range.

Format Cells window excel spreadsheet not adding up correctly

Cells with text format excel spreadsheet not adding up correctly

Repeat the above steps for the formula’s cell if that cell displays the function instead of a value.

Alternatively, you can add values in cells with text formatting without needing to convert them to general with this trick.

Paste Special window excel spreadsheet not adding up correctly

Those are a few of the best ways you can fix Excel spreadsheet formulas that aren’t adding up correctly.

In most cases, the SUM function doesn’t add up because it hasn’t been entered right or because its cell reference includes text cells.

If you know of any other way to fix this Excel-related issue, leave a message in the comment section below so other users can try it.

Basic SUM function not working

Thread tools.

Rate This Thread

I'm having a weird issue if anyone can help, I have a spreadsheet with various rows of data and at the end of each one I want to add a total column. (Sounds simple enough, and probably the first thing anyone learns on excel) I'm using the basic =SUM( select row cells ) and I'm getting 0 in the cell when there are clearly numbers to add up. It is also displaying a blue arrow, I have been into the formulas tab and selected remove arrows but whether it's there or not (although I have never had one show up before) the 'total' column SUM cells are just refusing to change from zero. If I retry the blue arrow reappears again no matter how many times I select remove arrows. I have never encountered this before and feel a bit silly asking for help on such a basic function but as it's so basic I'm struggling to find an alternate way to just add up the values in the cells. I'm using Excel 2007.

Re: Basic SUM function not working

It seems that the cells you are trying to sum are formatted as Text. They may look like numbers but in reality they are being treated as Text not numbers. Copy a blank cells (without any cell content in it) --> Select the cells you were trying to Sum --> Right click on one of the selected cell -> Choose Paste Special --> Select Add and click OK. Your Sum formula will return the correct output then.

Regards sktneer Treat people the way you want to be treated. Talk to people the way you want to be talked to. Respect is earned NOT given .

Thanks for trying but for some reason still not working. The cells were formatted to general, I have now tried with them in number, accounting, currency, general and text and tried your method with each one and nothing, still showing zero. It's really got me scratching my head!

Use a formula to test whether the cell content is a number. Say one of your cells in the range is A2, try a formula in another cell =ISNUMBER(A2). What do you get, True or False?

You may try something like this Assuming your range is A2:A10 Formula: Please Login or Register to view this content. if ( typeof(autoid) === 'undefined') autoid=0 ; else autoid++; var all_spans = document.getElementsByTagName( 'SPAN' ); for ( var i =0,skip =0 ; i "+ all_spans[i].innerHTML +" "; // show it all_spans[i].getElementsByTagName('img')[0].style.display='inline'; } } } if ( ZeroClipboard.flashEnabled() ) { //Create a new clipboard client var clip = new ZeroClipboard.Client(); //Glue the clipboard client to the last td in each row text = ZeroClipboard.getText ('PRE', 'bbcode_code', autoid ) ; clip.setText( text ) ; clip.glue("copytoclipboard_"+autoid); }

If you like my answer please click on * Add Reputation Don't forget to mark threads as "Solved" if your problem has been resolved "Nothing is so firmly believed as what we least know." -- Michel de Montaigne

It is also displaying a blue arrow, I have been into the formulas tab and selected remove arrows but whether it's there or not (although I have never had one show up before) the 'total' column SUM cells are just refusing to change from zero. If I retry the blue arrow reappears again no matter how many times I select remove arrows. The only reason that I know of for Excel to automatically display those arrows is if there is a circular reference present. Excel often displays 0 or some other non-answer for circular references, because it does not know how to resolve the circular logic. Assuming you have the status bar visible, look in the lower left corner of the Excel window and look for a "circular: reference ". If you have this circular reference notification, look through your spreadsheet (since it seems related to this =SUM() function, that might be as good a place as any to start), and see if you can figure out where your logic is going in circles and what you need to change so it is no longer circular.

Originally Posted by shg Mathematics is the native language of the natural world. Just trying to become literate.

Hi Guys, Thanks for all the different advice, numbers was coming up TRUE but I'd missed a circular reference with having a range of a full column that had one of the numbers to total in it. Cheers for the help back up and running now

Thread Information

Users browsing this thread.

There are currently 1 users browsing this thread. (0 members and 1 guests)

malfunction ,

View Tag Cloud

Posting Permissions

Excel formula not working

This article explains why your formula is not working properly, there are usually four different things that can go wrong.

1. Formula not working - check the function arguments

This example shows a function in cell B2 that returns a #VALUE! error, this may happen when there is something wrong with the arguments.

The SUBTOTAL function allows you to use a number between 1 and 11, the first argument is in this example 12 which is an invalid argument.

How to quickly show function arguments?

Type the function in a cell, and then the beginning parentheses, Excel instantly shows the arguments and in some cases, allowed values.

Formula not working display the function arguments 1

Here is a list of Excel functions and their arguments: Excel functions

Here is a list of common errors: Excel formula errors

2. How to solve "numbers stored as text"?

The first one is numbers stored as text, demonstrated in the picture above.

Why is the SUM function in cell C7 returning 0 (zero) in the picture above? The numbers in cell range C3:C6 are stored as text. You can see that by the green arrow in each cell's top left corner.

Why is this happening? The cell range C3:C6 was formatted as text cells before you entered the numbers in these cells.

How to solve it? Select cell range C3:C6, and press with left mouse button on the exclamation mark symbol (or exclamation point if you are American) to open a menu. Press with mouse on "Convert to Number", the green arrows disappear and the SUM function formula works as expected again.

3. How to change the "manual calculation" setting

The SUM formula in cell C7 returns 0 (zero), why is this happening?

Check if your workbook is in manual calculation mode. Go to tab "Formulas" on the ribbon, then press with left mouse button on the "Calculations Options" button.

In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.

4. The cell shows the formula and not the result - here is how to fix it

Cell C7 displays the formula, not the result. Why is this happening?

Go to tab "Formulas", check if "Show Formulas" button is enabled (highlighted).

Press with mouse on the "Show Formulas" button to disable it. The cell now shows the result of the SUM calculation. If this is not working, read section 5 below.

5. Formula cell formatted as text

The cell in C7 shows the formula? I don't want that, I want to see the calculation result.

Select cell C7, press CTRL + 1 on your keyboard.

This reveals that the cell is formatted as "Text" making it show the formula and not the result. Press with mouse on "General" and then on the OK button.

Now it shows the output from the formula.

6. Formula not working - #NAME? error

The #NAME? error hints that you misspelled the function name.

Tip! Check the suggestions while typing the formula. Use the arrow up and down keys to change the selected suggestion. Use the TAB key to auto-complete the selected suggestion.

7. How to troubleshoot a formula not working as intended?

The image above shows a formula in cell C6 that returns no error, however, the calculation is wrong. How do we troubleshoot this formula to find what is wrong in the calculation?

The "Evaluate formula" tool lets us see the calculation steps in greater detail. Here is how to start the tool:

Formula not working how to troubleshoot a formula using the evaluate formula tool

A dialog box appears.

Formula not working evaluate formula tool

The dialog box shows the formula in the white box and has four buttons below. The underlined expression shows what part of the formula will be calculated in the next step.

The italicized text is the most recent result, press with left mouse button on the "Evaluate" button to start the formula evaluation.

Formula not working evaluate formula tool1

Keep press with left mouse button oning on the "Evaluate" button until greyed out to see all steps in the calculation, this helps to identify where the problem is.

The tool shows that the formula multiples values in cells C2 and C3 before adding the number in cell C4. We need to add C2 with C4 and then multiply with C3.

Press with left mouse button on the "Close" button to dismiss the dialog box.

The parentheses allow us to control the order of operation. Here is the final formula:

Formula not working evaluate formula tool2

Sum category

Excel formula categories

Excel categories.

How to comment

How to add a formula to your comment <code>Insert your formula here.</code> Convert less than and larger than signs Use html character entities instead of less than and larger than signs. < becomes < and > becomes >

How to add VBA code to your comment [vb 1="vbnet" language=","] Put your VBA code here. [/vb]

How to add a picture to your comment: Upload picture to postimage.org or imgur Paste image link to your comment.

Contact Oscar

You can contact me through this contact form

Formula categories

Search website

IMAGES

Sum Formula Output » NetworkUstad
Using the SUM function effectively in Microsoft Excel
SUM Formula in Excel
How to Sum in Excel and All Its Formulas/Functions
How to use SUM in Excel
Approaching problem-solving in Ms Excel

VIDEO

Can you Answer the riddle
Find The Value 🔥💯🧩 #shorts #shortvideo #ssc #ssccgl #sscgd #railway #sscchsl #maths #trending #viral
DERMA PROGENIX REVIEW: SEE THIS NOW! DERMA PROGENIX DOES IT WORK? DERMA PROGENIX WHERE TO BUY?
Day 7 : 2 sum problem || Bruit force approach || C++
How to SUM in Excel With Any Type Errors ? MS Excel Tips and Tricks 2022
Hello friends let solve sum of the problem

COMMENTS

Excel SUM Function Not Working?
Why the SUM formula in Microsoft Excel doesn't work?. This short tutorial will give you a solution to the problem of the SUM formula not
How to use the SUM function in Microsoft Excel
Add values in Microsoft Excel with the SUM function. You can add individual values, cell references, ranges, or a mix of all three.
SUM function
=SUM(A2:A10) Adds the values in cells A2:10. · =SUM(A2:A10, C2:C10) Adds the values in cells A2:10, as well as cells C2:C10.
How to correct a #VALUE! error in AVERAGE or SUM functions
This is an array formula. So you either need to press ENTER (only if you are using Microsoft 365) or CTRL+SHIFT+ENTER in the formula. Note: If you have a
How to use AutoSum in Excel
The most common reason for AutoSum not working in Excel is numbers formatted as text. At first sight, those values may look like normal numbers
Excel Sum Function Examples
The quickest and easiest way to sum a range of cells is to use the Excel AutoSum button. It automatically enters an Excel SUM function in the
Excel Sum Not Working? Here's How To Fix It
How to Fix the SUM Function in Excel? · Look for Typos · Change Format · Replace Unsupported Symbol · Disable Show Formulas · Repair the Office App (
Excel SUM Formula Not Adding Correctly: How to Fix
The cell format is set to text: If the cell format is set to text, the SUM formula will not work correctly. You can fix this by selecting the
[SOLVED] Basic SUM function not working
Copy a blank cells (without any cell content in it) --> Select the cells you were trying to Sum --> Right click on one of the selected cell ->
Excel formula not working
How to solve it? Select cell range C3:C6, and press with left mouse button on the exclamation mark symbol (or exclamation point if you are