Cellwise Start free

Excel guide · 9 min read

Excel formula returns 0 instead of the value: 7 causes and fixes

Quick answerWhen an Excel formula returns 0, the formula usually works but finds nothing it can use. The most common causes: the numbers (or dates) are stored as text, so SUM, SUMIF and SUMIFS skip them; the criteria match nothing because of a hidden space; the cells hold TRUE/FALSE, which SUM ignores; or a lookup found an empty cell. Check with =COUNT(range): if it returns 0 for cells that clearly hold numbers, they are text.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJ
1OrderDateRegionUnitsRevenueShippedDiscountRegion to totalWest
210412026-02-26West121440TRUE0.05Site visits4800
310422026-03-02North8960TRUESign-ups12
410432026-03-05West202300FALSE0.1
510442026-03-09South5625TRUENight shift8:00 AM
610452026-03-12West161840FALSE0.05Night shift8:00 AM
710462026-03-16North91035TRUE0.05Night shift8:00 AM

Download this sheet as an Excel practice file (.xlsx)

First check: is it really 0, or only shown as 0?

Before you hunt for a broken formula, make sure the value is actually zero. The sample has 12 sign-ups from 4,800 site visits. =J3/J2 returns 0.0025, but in a cell formatted as 0% (no decimals) it shows 0%. The same happens with a currency format on a value like 0.004, or a Number format with no decimal places on 0.4.

Select the cell and press Ctrl+Shift+~ to apply the General format, or click Home > Increase Decimal a few times. If digits appear, the formula was right all along and only the format needs decimals: 0.00% shows 0.25% here.

Sometimes the 0 is real because a formula rounded it away. =ROUND(J3/J2,2) really does return 0, because 0.0025 rounded to two decimals is 0.00. Percentages are fractions, so a rate needs at least four decimals in ROUND (0.0025 is 0.25%), or leave it unrounded and let the format do the work.

=J3/J2

Result: 0.0025 shows as 0% with Percent format

0.0025, which a 0% format shows as 0%. Formatted as 0.00% it shows 0.25%.

Cause 1: every number is stored as text, so SUM returns 0

The units in column D came from a web-shop export as text. They look like numbers, but they sit on the left of the cell, usually with a small green triangle. SUM, SUMIF, SUMIFS and AVERAGE skip text without an error, so when every value in the range is text, the result is exactly 0. When only some are text you get a total that is too small instead, which is harder to spot.

The quick test is =COUNT(D2:D7). COUNT counts only real numbers, and here it returns 0 for six filled cells (COUNTA returns 6). That one check settles it.

A confusing detail: =D2+D3 returns 20, because the + operator converts text that looks like a number. That is why a hand-typed formula can work while SUM on the same cells shows 0. SUMIF has the same weakness in its sum range: =SUMIF(C2:C7,"West",D2:D7) returns 0, while =SUMPRODUCT((C2:C7="West")*D2:D7) returns 48, because multiplying converts the text.

Fix the data once rather than every formula: select the column, then Data > Text to Columns > Finish, or click the warning triangle and choose Convert to Number. Changing the cell format to Number does not convert values that are already text. If the export is refreshed every day and you cannot change it, convert inside the formula: =SUMPRODUCT(--D2:D7) returns 70. All the methods are in convert numbers stored as text.

=SUM(D2:D7)

Result: 0

0: all six units are text, so SUM finds no numbers. =SUMPRODUCT(--D2:D7) returns the real total, 70.

Cause 2: the cells hold TRUE/FALSE (checkboxes)

Column F is a Shipped checkbox. A checkbox cell (Insert > Checkbox in Excel 365) or a cell linked to an older form-control checkbox holds TRUE or FALSE, not 1 or 0. SUM ignores TRUE and FALSE in a range, so =SUM(F2:F7) returns 0 however many boxes are ticked.

Count the ticked boxes with =COUNTIF(F2:F7,TRUE), which returns 4. To add up revenue for the ticked rows, use the checkbox column as a criterion: =SUMIFS(E2:E7,F2:F7,TRUE) returns 4,060. If you prefer arithmetic, the double minus turns TRUE into 1: =SUMPRODUCT(--F2:F7) also returns 4. Convert TRUE/FALSE to 1 and 0 compares --, *1 and IF and covers completion rates from a checkbox column.

As with text numbers, + behaves differently: =F2+F3 returns 2, because the operator converts TRUE to 1. Only functions that take a range, such as SUM and AVERAGE, skip the logical values. More on checkbox formulas in insert a checkbox in Excel.

=SUM(F2:F7)

Result: 0

0: SUM skips TRUE and FALSE. =COUNTIF(F2:F7,TRUE) counts the 4 ticked boxes.

Cause 3: SUMIF or COUNTIF criteria that match nothing

A conditional formula returns 0 when no row matches its criteria, and the reason is usually invisible. J1 holds the region to total, typed as "West " with a space at the end. =SUMIF(C2:C7,J1,E2:E7) looks for "West " and finds nothing, so it returns 0. =LEN(J1) returns 5 for a four-letter word, which gives the space away.

Wrap the criteria in TRIM: =SUMIF(C2:C7,TRIM(J1),E2:E7) returns 5,580, the three West orders. If the extra spaces are in the data instead of the criteria cell, clean the column with TRIM (remove spaces in Excel).

Other criteria that silently match nothing: a typo or a different spelling ("N. America" vs "North America"), a criteria cell that holds a number while the column holds the same values as text, and an operator outside the quotes. ">"&100 works; >100 without quotes is not accepted. SUMIF returning 0 tests every one of these, including ranges that do not line up.

=SUMIF(C2:C7,J1,E2:E7)

Result: 0

0: "West " with a trailing space matches none of the regions. With TRIM(J1) the same formula returns 5,580.

Cause 4: date criteria on dates stored as text

Dates are numbers in Excel, so ">="&DATE(2026,3,1) only matches cells that hold real dates. The dates in column B were imported as text (=ISTEXT(B2) returns TRUE), so =SUMIFS(E2:E7,B2:B7,">="&DATE(2026,3,1)) matches no rows and returns 0, even though five orders are clearly in March.

Text dates give themselves away: they are left-aligned, changing the format to a date changes nothing, and they do not sort in date order. Convert the column with Data > Text to Columns > Finish (choose the matching date order in step 3 if the text is not year-month-day), or see convert text to a date for formats Excel does not recognize.

If you cannot change the source, convert inside the formula: =SUMPRODUCT((--B2:B7>=DATE(2026,3,1))*E2:E7) returns 6,760. The double minus only works on text Excel recognizes as a date in your regional settings; year-month-day, as here, is the safest. Build criteria with DATE rather than typing ">=3/1/2026", which depends on the regional date order. More in SUMIFS with a date range.

=SUMIFS(E2:E7,B2:B7,">="&DATE(2026,3,1))

Result: 0

0: the dates are text, so no row is on or after March 1. Converted with --, the March revenue is 6,760.

Cause 5: times that add up to exactly 24 hours show 0:00

Three 8-hour night shifts in J5:J7 add up to 24 hours. Excel stores time as a fraction of a day, so the total is exactly 1. In the h:mm format, which counts hours within one day, 1 day shows as 0:00, and 26 hours would show as 2:00.

Format the total as [h]:mm (Home > Number Format > More Number Formats > Custom). The square brackets let the hours run past 24, so the same total shows 24:00. For pay or decimal hours, multiply by 24: =SUM(J5:J7)*24 returns 24. See sum time in Excel for totals across a week.

=SUM(J5:J7)

Result: 1 shows as 0:00 with the custom h:mm format

1 (one whole day), which h:mm shows as 0:00. Formatted as [h]:mm it shows 24:00.

Cause 6: a lookup or reference that lands on an empty cell

Order 1042 has no discount, so G3 is empty. =XLOOKUP(1042,A2:A7,G2:G7) returns 0, and VLOOKUP with FALSE does the same. Excel turns an empty cell into 0 whenever a formula returns it, and a plain =G3 in another cell shows 0 too. A 0 looks like "no discount" when the truth may be "not filled in yet".

To show the difference, test the result before returning it: =LET(d,XLOOKUP(1042,A2:A7,G2:G7),IF(d="","not set",d)) returns "not set" for 1042 and 0.05 for 1041. The other options, and why the popular &"" shortcut turns numbers into text, are in lookup returns 0 instead of blank.

The same effect explains a column of zeros after you fill a formula down. Say a tax rate sits in H1 and row 2 reads =E2*H1. Filled down, row 3 becomes =E3*H2, row 4 =E4*H3, and so on, each pointing at an empty cell, so every row after the first returns 0. Lock the fixed cell with $H$1 (absolute references).

=XLOOKUP(1042,A2:A7,G2:G7)

Result: 0

0: the matched cell G3 is empty, and Excel returns an empty cell as 0.

Cause 7: a circular reference

A formula that includes its own cell, such as =SUM(E2:E8) typed into E8, is a circular reference. Excel shows a warning when you enter it, and once you close the warning the cell usually shows 0 (or the last value it calculated) with "Circular References" in the status bar. It often happens when a total row is added and the formula range is stretched one row too far.

If iterative calculation is switched on (File > Options > Formulas > Enable iterative calculation), Excel stops warning you and the wrong value stays. Turn it off unless the model needs it on purpose. Full steps are in how to find a circular reference in Excel.

  1. Go to Formulas > Error Checking (the arrow next to it) > Circular References. Excel lists the cell; click it to jump there.
  2. Press F2 on the formula and check the colored range outlines: the formula's own cell must not be inside them.
  3. Change the range so it stops one row above the formula, for example =SUM(E2:E7) in E8.

Find the cause in 30 seconds

Work through these in order. Each one rules out a cause with a single check, and the first two catch most cases.

  • Apply the General format (Ctrl+Shift+~). Digits appear: the value was never 0, the format hid the decimals.
  • =COUNT(range) returns 0 or less than the filled cells: numbers stored as text (Cause 1).
  • The cells show TRUE/FALSE or checkboxes: use COUNTIF(range,TRUE) or a SUMIFS criterion (Cause 2).
  • =LEN(criteria cell) is longer than the visible text: hidden spaces (Cause 3).
  • =ISTEXT(first date) is TRUE: dates stored as text (Cause 4).
  • The total is a time and shows 0:00: use [h]:mm (Cause 5).
  • A lookup or a filled-down formula: check what cell it actually lands on with F2 (Cause 6).
  • "Circular References" in the status bar: Cause 7.
  • Still 0 and nothing above fits: check Formulas > Calculation Options is Automatic, then go through why is my Excel formula not working.

Common mistakes

Changing the format to Number to fix text numbers

The format changes how a value is shown, not what it is. Text stays text until you convert it with Text to Columns > Finish or Convert to Number.

Wrapping the formula in IFERROR

A 0 is not an error, so IFERROR changes nothing. Find which cause applies with the checks above.

Hiding zeros with a number format

A format such as 0;-0;;@ makes the 0 disappear, but the total is still wrong. Use it only once you know the 0 is genuine.

Rounding a rate to 2 decimals

=ROUND(0.0025,2) is 0. Rates are fractions: round to 4 decimals or leave the value unrounded and format it as a percentage.

FAQ

Why does my SUM formula return 0?

Almost always because the numbers are stored as text, often after an import or copy from a web page. Test with =COUNT(range): if it returns 0, convert the column with Data > Text to Columns > Finish. If the cells hold TRUE/FALSE, SUM ignores those too; use COUNTIF(range,TRUE).

Why does Excel show 0 instead of the value?

Either the value is a small decimal and the number format has no decimal places (0.4 shows as 0, 0.25% shows as 0%), or the formula refers to an empty cell. Apply the General format with Ctrl+Shift+~ to see the stored value.

Why does my formula return 0 instead of text?

A lookup or reference that lands on an empty cell returns 0, not a blank. Test the result first, for example =LET(r,XLOOKUP(...),IF(r="","",r)), so empty matches come back empty.

Why does COUNTIF return 0 when the values are there?

The criteria do not match exactly: a trailing space, a different spelling, or a number criterion such as ">10" on numbers stored as text, which COUNTIF does not treat as numbers. Check the criteria cell with LEN and the range with COUNT.

Why does my total of hours show 0:00?

The hours add up to exactly 24 (or a multiple of 24) and the cell uses h:mm, which shows hours within one day. Format the total as [h]:mm to show 24:00 or more.

Functions used

Related guides

Updated