Excel formula not working
This article explains why your formula is not working properly, there are usually four different things that can go wrong.
Table of Contents
1. Number 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, 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.
2. Manual calculation
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.
3. The cell shows the formula and not the result
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.
4. 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.
Weekly Blog EMAIL
[newsletter_signup_form id=1]
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
Running totals based on criteria
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
The formula in cell D3 adds all unique numbers in cell range B3:B12 and returns the total. Unique values are all […]
Find empty cells and sum cells above
This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
Question: It's easy to sum a list by multiple criteria, you just use array formula a la: =SUM((column_plane=TRUE)*(column_countries="USA")*(column_producer="Boeing")*(column_to_sum)) But all […]
Sum numerical ranges between two numbers
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
To extract groups from cell range B3:B10 I use the following regular formula in cell B13. Weekly Blog EMAIL [newsletter_signup_form id=1] […]
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
Leave a Reply
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.
