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
This article demonstrates a VBA macro that saves user input to a given workbook and worksheet programmatically. Macros are great […]
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Highlight group of values in an x y scatter chart programmatically
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
How to use the WITH … END WITH statement
The With ... End With statement allows you to write shorter code by referring to an object only once instead […]
How to use the SUMIFS function
The SUMIFS function calculates a total based on multiple criteria, it has been available in Excel since version 2010. I […]
Search each column for a string each and return multiple records – OR logic
RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]
This article demonstrates how to find empty cells and populate them automatically with a formula that adds numbers above and […]
This article demonstrates a formula that calculates a running total. A running total is a sum that adds new numbers […]
The easiest way to sum a cell range is to simply select the cell range and read the values in […]
Andrew asks: LOVE this example, my issue/need is, I need to add the results. So instead of States and Names, […]
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 […]
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] […]
Katie asks: I have 57 sheets many of which are linked together by formulas, I need to get numbers from […]
The formula in cell C15 uses two dates two to filter and then sum values in column C, the SUMIFS […]
This article explains how to build an array formula that sums numerical ranges. Example, I want to know how to […]
The SUBTOTAL function lets you sum values in a cell range that have some rows hidden or filtered, the picture […]
The image above shows numbers in column B, some of these numbers are duplicates. The formula in D12 adds unique […]
Table of Contents Sum unique numbers Get Excel *.xlsx file Sum unique distinct numbers Get Excel *.xlsx file Sum number […]
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two […]
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.
