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

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

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 .

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

## Was this information helpful?

## How to use AutoSum in Excel

- Where is AutoSum in Excel?
- How to do AutoSum in Excel
- Excel Sum shortcut
- Using AutoSum with other functions
- How to AutoSum only visible cells

## 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

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

- To sum a column , select the cell immediately below the last value in the column.
- To sum a row , select the cell to the right of the last number in the row.

## Shortcut for Sum in Excel

## How to use AutoSum with other functions

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

- AVERAGE - to return the average (arithmetic mean) of numbers.
- COUNT - to count cells with numbers.
- MAX - to get the largest value.
- MIN - to get the smallest value.

## How to AutoSum only visible (filtered) cells in Excel

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

## How to sum selected cells vertically and horizontally

## How to copy an AutoSum formula to other cells

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

## You may also be interested in

- Excel Sum formula examples to total a column, rows or only visible cells
- SUMIF in Excel - formula examples to conditionally sum cells
- How to use Excel SUMIFS and SUMIF with multiple criteria
- How to use Excel SUMIFS and SUMIF with multiple criteria - formula examples
- How to use VLOOKUP & SUM or SUMIF functions in Excel

Overview: 7 Ways to Sum in Excel

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 Filtered List With SUBTOTAL

Sum Filtered List With AGGREGATE

Sum Specific Items in Filtered List

## More Function Tutorials

Last updated: February 24, 2023 3:42 PM

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

## Why is Excel Sum Not Working?

- Typing Error : We almost always overlook typing errors. However, even the most minor typing error can cause the most complex functions not to work.
- Unsupported Format : In Excel, you have the feature to change the format of the data you’ve entered. If SUM isn’t working, the data you’re trying to manipulate may be set to text. Excel won’t add data that is set to the text format.
- Unrecognized Symbol Used : This is mostly relevant to decimal and thousands separators. By default, the period and comma symbol, respectively, are used for each divider. If you’ve used these symbols incorrectly, the SUM function won’t work.
- Show Formulas Enabled : You could’ve accidentally enabled the Show Formulas feature. When this feature is active, Excel will display the formula used to calculate the result instead of viewing the result.
- Manual Calculation Enabled : If Excel doesn’t automatically recalculate the data you’ve changed from the selected cells, you’ve enabled Manual calculation.
- Corrupted Program Files : The program files of your Excel may have gone corrupt. When the files get corrupted, the application won’t be able to complete some to all of its functions.

## How to Fix the SUM Function in Excel?

## Look for Typos

## Change Format

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

- Open MS Excel to open your workbook.
- From the Menubar, make sure you’re on Home .
- On the Home Ribbon, locate the Number section.

## Replace Unsupported Symbol

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

- Open your document from Excel.
- From your keyboard, hit the combination Ctrl + F .
- In the Find and Replace window, head on to the Replace tab.

## Disable Show Formulas

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

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

- Open your workbook from Microsoft Excel.
- Hop on to the Formulas tab from the menu bar.
- Navigate to the Calculations section.

## Repair the Office App (Windows)

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

- Use the combination Windows key + I to open the Settings application on your keyboard.
- From the panel to your left, select Apps .
- Hop on to Apps & Features , then scroll to Office .
- Next to the Office app, select the vertical three-dot menu .

## Related Posts

## 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

## Leave A Reply Cancel Reply

Save my name, email, and website in this browser for the next time I comment.

Type above and press Enter to search. Press Esc to cancel.

## Excel SUM Formula Not Adding Correctly: How to Fix

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

- Having issues with the Excel SUM formula not adding properly? Worry not, we got the solution.
- Remember that you need to respect the formula syntax, so be sure you add it with the right commands.
- Another potential cause may be the formatting of text as they can stop the SUM formula from showing up.
- Navigate down below to find and apply the steps that will surely get you out of this issue.

- Download DriverFix (verified download file).
- Click Start Scan to find all problematic drivers.
- Click Update Drivers to get new versions and avoid system malfunctionings.
- DriverFix has been downloaded by 0 readers this month.

- The formula is using the wrong cell references : Make sure you are using the correct cell references in your formula, and that the cells being referenced are actually part of the data you want to sum.
- There are hidden rows or columns that are being included in the calculation : If you have hidden rows or columns in your data, make sure they are not being included in the calculation by accident. You can check this by looking at the cell references in the formula and seeing if they include any hidden cells.
- There are cells with text or errors in them : If there are cells in the range being summed that contain text or errors, the SUM formula will not work correctly. You can fix this by making sure all cells being summed contain only numerical data.
- 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 cells you want to sum, and then going to the Home tab in the ribbon and clicking on the “Number” dropdown in the “Number” group. Choose “General” or another number format from the list.
- The formula has been entered incorrectly : Make sure you are using the correct syntax for the SUM formula, including the equal sign (=) and the open and close parentheses.
- How can I fix Excel SUM functions that don’t add up?

## 1. Check the syntax of the SUM function

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

The syntax for the SUM function is:

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

- Right-click an empty cell and select Copy .
- Select all the text cells with the function’s cell range.
- Click the Paste button.

- Select the Values and Add options there.
- Click the OK button. Then the function will add the numbers in the text cells.

## Leave a Reply Cancel reply

Save information for future comments

- Microsoft Office Application Help - Excel Help forum
- Excel General
- [SOLVED] Basic SUM function not working

## Basic SUM function not working

## 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

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

## Similar Threads

## Posting Permissions

- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
- BB code is On
- Smilies are On
- [IMG] code is Off
- HTML code is Off
- Trackbacks are Off
- Pingbacks are Off
- Refbacks are Off
- ExcelForum.com

- Features Excel Basics Excel Defined Table Advanced Filter Data Validation Drop-down lists Named Ranges Solver
- Conditional formatting
- Pivot table
- VBA VBA Functions Methods Properties Statements Macros User defined functions Files & folders Checkboxes
- Archive All articles sorted by date Categories Products
- Contact About me Blogs I read

## Excel formula not working

## Table of Contents

- Formula not working - check arguments
- Formula not working - Number stored as text
- How to change the "manual calculation" setting
- Formula not working - the cell shows the formula and not the result
- Formula not working - formula cell formatted as text
- Formula not working - #NAME! error
- How to troubleshoot a formula not working as intended

## 1. Formula not working - check the function arguments

How to quickly show function arguments?

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.

## 3. How to change the "manual calculation" setting

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

## 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).

## 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.

Now it shows the output from the formula.

## 6. Formula not working - #NAME? error

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

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

- Select the cell you want to troubleshoot, in this example cell C6.
- Go to the tab "Formulas" on the ribbon.

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:

## Sum category

## Excel formula categories

## Leave a Reply

Mail (will not be published) (required)

## How to comment

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

You can contact me through this contact form

## IMAGES

## VIDEO

## COMMENTS

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

Add values in Microsoft Excel with the SUM function. You can add individual values, cell references, ranges, or a mix of all three.

=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.

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

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

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

How to Fix the SUM Function in Excel? · Look for Typos · Change Format · Replace Unsupported Symbol · Disable Show Formulas · Repair the Office App (

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

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 ->

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