Level 4 · Quiz: SUM vs + on numbers stored as text

Two formulas, the same three cells, two different totals. Neither shows an error. This is the trap from the last level seen from the other side, and it’s the kind of question that settles an argument at the stock-count table.

Ranges inside functions keep only numbers

When SUM, AVERAGE, MIN, MAX or COUNT receive a range, they look at each cell and keep the numbers. Text, empty cells and TRUE/FALSE are left out, without a warning. That is a feature (a header row inside the range does no harm) and a trap (a number stored as text is dropped too).

Operators convert

+, -, * and / need numbers, so Excel tries to turn whatever they get into one. The text "8" becomes 8. The text "n/a" can’t be converted, and the result is #VALUE!. That is why =E2*1 and =--E2 work as quick conversions.

Formula	B3 = "8"	B3 = "n/a"
=SUM(B2:B4)	17	17
=B2+B3+B4	25	#VALUE!
=SUM(B2,VALUE(B3),B4)	25	#VALUE!
=COUNTA(B2:B4)-COUNT(B2:B4)	1	1

Which one to trust

Neither, until the column is clean. SUM is wrong without telling you. + is right here by luck and breaks on the first non-numeric value. The reliable path is the one from the last level: count the text cells, convert the column with VALUE, then SUM the converted column. An error from VALUE points at a cell that needs a person.

The same trap in other functions

The rule “ranges keep only numbers” is not special to SUM. On the refunds sheet from the last level, =AVERAGE(E2:E45) averages only the 34 numeric refunds and ignores the ten text ones, so the average is off as well as the total. =MAX(E2:E45) returns 779.00, while the largest refund in the month is the 982.60 sitting in E38 as text. SUMIF and COUNTIF follow the same rule on the cells they add up. One text column can quietly skew every summary built on it, which is why the check comes first and the summaries second.

Google Sheets behaves the same way for all of these, so a sheet that moves between the two keeps the problem. The habit that travels is the count: before any summary of a column you didn’t type yourself, compare COUNT with the number of rows.

Common mistakes

• Reading alignment as decoration. A left-aligned number in a column of right-aligned ones is text until proven otherwise.
• Adding cells one by one. =B2+B3+B4 doesn’t grow when a fourth shelf is added; a range does.
• Assuming a format fixes it. Formatting B3 as Number doesn’t change the text inside it.