digestVidSign in
summary

excelisfun - Excel Magic Trick 1222: Formula To Add 10kg, 6kg, 19kg: Array Formula Or Custom Number Format?

Thumbnail of the video “excelisfun - Excel Magic Trick 1222: Formula To Add 10kg, 6kg, 19kg: Array Formula Or Custom Number Format?”
Community upload. AI output has not been independently verified.
Author: excelisfun
Categories: Science Technology
Tags: science, technology
Views: 2 · Last viewed: 2026-10-16 00:54 UTC

Short Summary

Main recommendation: To add numbers that display with "kg" in Excel, apply a custom number format (0"kg") so the cells stay numeric and SUM works, or, if the "kg" is stored as text, strip it with SUBSTITUTE, convert the result back to a number, and add it with SUMPRODUCT, which handles the array without Control+Shift+Enter. The video contrasts these two approaches and explains why the text version returns zero.

Key Points

  • A column that looks like numbers can return 0 from SUM if the entries are text (for example, "44kg" typed as text), because SUM ignores text.
  • Right alignment applied in the Home ribbon can make text look like a number; the alignment does not change the data type.
  • The custom number format 0"kg" displays "kg" on screen while keeping the underlying value numeric, so SUM adds it correctly.
  • When the "kg" must remain in the data, SUBSTITUTE can remove it, and an extra math operation (+0) converts the result back to a number.
  • SUMPRODUCT can sum an array result without the special Control+Shift+Enter keystroke.

Details Worth Noting

Concrete instructions:

  • Custom number format method: select the column's numeric cells, press Ctrl+1, choose Number, go to Custom, and in the Type box enter 0"kg" (zero, then "kg" in double quotes). Any number typed in the column will then show "kg" while remaining a number.
  • Check for text: press F2 on a suspect cell to see if it contains a number plus "kg" (indicating text); AutoSum (Alt+=) was used first to show the column returns zero.
  • Helper column method: in a new column, enter =SUBSTITUTE(A1,"kg","")+0 (replace A1 with the cell reference), then fill it down. The SUBSTITUTE removes "kg," and +0 converts the text back to a number.
  • Single-cell array method: enter =SUMPRODUCT(SUBSTITUTE(range,"kg","")+0) using the full column range, then press Enter. No Control+Shift+Enter is needed.
  • To verify an array's conversion, select the array expression in the formula bar and press F9 to see the evaluated values; press Ctrl+Z to undo the change.
  • Use SUMPRODUCT with a single array when your goal is to add an array operation's results.
  • The video notes that if the "kg" is only a display choice, the custom number format is the simpler route, and the SUBSTITUTE approach is for cases where the "kg" must stay in the source data (for example, a data dump).
  • The presenter cautions that an array result cannot be displayed all at once in one cell, so use F9 to inspect values rather than expecting them to show in the cell.

Bottom Line

Keep numbers numeric: format them for display with custom number formats when possible, and use SUBSTITUTE with +0 and SUMPRODUCT only when the unit text must stay in the source data.