How to correctly sum numbers with units in a range in Excel?
When working with Excel, you may encounter a situation where you need to sum numbers that include units, such as "5 kg", "12 lbs", or "10 m". This is common, for example, when tracking weights, lengths, or amounts—especially in purchase lists, engineering measurements, laboratory logs, and logistics data where each value is appended with a measurement unit. However, if you try to use Excel’s standard SUM formula on such data, it won’t work as expected. This is because Excel interprets values containing text (like units) as text strings rather than numerical values, meaning SUM and other basic math functions ignore them or return errors. Accurately summing these values requires a method to extract the numeric portion before carrying out the calculation. This tutorial will guide you through several practical and effective methods, including formulas, built-in features, and handy add-ins like Kutools for Excel, to help you sum numbers with units correctly in different real-world scenarios.
➤ Sum numbers with units using a formula
➤ Sum numbers with units using Kutools for Excel
➤ Sum numbers based on same unit with Kutools for Excel
➤ Combine duplicate rows and sum corresponding numbers using Kutools for Excel
➤ VBA macro: Extract numbers from text strings with units and sum automatically
➤ Use Power Query to Extract and Sum Numbers with Units
Sum numbers with units using a formula
When all your values share the same unit and you need a quick, formula-based solution, Excel array formulas can help extract the numeric part and calculate the sum. This method is suitable for ranges where every cell contains a number immediately followed by the exact same unit, for example, "8 kg", "15 kg", etc.
Select a blank cell for your result and enter the following formula:
=SUM(SUBSTITUTE(B2:B7, "kg", "") +0)After typing the formula, confirm it by pressing Ctrl + Shift + Enter together (on some systems, this is required for array formulas), so Excel recognizes it as an array operation. You’ll then see the sum of the numeric portions in your selected cell.
Tip: In the above formula, B2:B7 refers to the data range containing the values with units that you want to add up, and "kg" is the unit included in your data. Make sure that all entries in the range are consistent in unit and that there are no extra spaces or discrepancies in spelling, or the formula will not extract values correctly. If your unit or range is different, adjust both accordingly. This formula does not handle scenarios where multiple types of units are present in the range.
Sum numbers with units using Kutools for Excel
For users who prefer an easier, more visual approach or want to avoid memorizing formulas, Kutools for Excel offers a direct way to clean up numeric values. Its Remove Characters feature makes it easy to strip out the non-numeric unit so you can sum the numbers directly. This is ideal for situations where your data contains numbers and units in one column and you need to quickly make the numbers usable for calculations.
1. Highlight the cells containing the numbers with units. Go to the Kutools tab, click Text, and select Remove Characters. See screenshot:
2. In the Remove Characters dialog box, select the Non-numeric option only. This setting removes all characters except numbers. See screenshot:
3. Click OK or Apply. Now, only the numeric contents remain in your selected cells, which you can now sum up with a simple formula. Select a blank cell and input the formula below to get your result:
=SUM(B2:B7)The image below shows what the cleaned column and the sum look like following this procedure.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Note: Kutools processes direct replacements in the selected cells, so you may want to work on a copy of your data if you need to preserve the original text. This solution works best for single-type units. After using this feature, ensure all cleaned values are numeric before summing.
Sum Numbers With Same Unit Using Kutools for Excel and SUM function
Sum numbers based on same unit with Kutools for Excel
If your table contains values with different units—for instance, weights in both "Kg" and "Pound"—and you want to sum separately for each unit (such as getting the total Kg and total Pound amounts), Kutools provides an efficient way with its Formula Helper feature. This is useful for summary tables, inventory reports, or when preparing data for further comparison.
Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy permanently free AI features! Download now!
1. Choose the cell where you want the summed result to appear. Navigate to Kutools > Formula Helper > Statistical > Sum based on same text.
2. In the Formulas Helper dialog window, set up your calculation: under Arguments input, pick the full data range as your Range, and type or select the specific unit (such as "Kg" or "Pound") in the Text box. Then click OK.
Tip: The default formula uses absolute references (e.g., $B$2:$B$7). If you plan to auto-fill this function for multiple units or rows, manually change references to relative as needed.
Now, your result cell displays the total for the chosen unit. Repeat for each unit to get all group totals.
Note: This method works well for tables with distinctly labeled unit types and helps to automate multi-unit summing, minimizing error from manual grouping.
Combine duplicate row and sum corresponding numbers using Kutools for Excel
Some data sets feature items or entities that are listed more than once with associated quantities and units, such as fruit with weight entries in different units. Suppose you want to sum up values grouped by item and unit, merging duplicate rows and consolidating the total. Kutools for Excel’s Advanced Combine Rows function is designed for just such scenarios, making aggregation fast and straightforward.
1. Select your table containing duplicate items and mixed units, then go to Kutools > Merge & Split > Advanced Combine Rows.
2. In the Combine Rows Based on Column dialog, first select the column containing the units and mark it as Primary Key. This lets Kutools group your data by unit. See screenshot:
3. Next, highlight the column with the numeric values (like "Weight"), then select Calculate and pick Sum to have values added together for each group.
4. Click OK and your table will summarize by unit, combining numbers as required. This is particularly useful for inventory and sales data where multiple entries need to be aggregated by matching unit.
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now
Tip: You can group by more than one key (e.g., item and unit) for deeper consolidation.
Combine Duplicates And Sum Numbers
VBA macro: Extract numbers from text strings with units and sum automatically
For advanced users looking for full automation or working with complex or varied patterns in unit strings, a VBA macro can handle extraction and summing in one go. This method is especially helpful when your range contains numbers followed by different units or inconsistent data structures, and when built-in Excel formulas are too limiting or cumbersome to adjust for each case.
1. Open the VBA Editor by clicking Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, then paste the following code into the module:
Sub SumNumbersWithUnits()
Dim cell As Range
Dim WorkRng As Range
Dim xTitleId As String
Dim NumSum As Double
Dim NumStr As String
Dim i As Long
Dim ch As String
On Error Resume Next
xTitleId = "Kutools for Excel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range with numbers and units", xTitleId, WorkRng.Address, Type:=8)
NumSum = 0
For Each cell In WorkRng
NumStr = ""
For i = 1 To Len(cell.Value)
ch = Mid(cell.Value, i, 1)
If ch Like "[0-9.]" Then
NumStr = NumStr & ch
ElseIf Len(NumStr) > 0 Then
Exit For
End If
Next i
If IsNumeric(NumStr) Then
NumSum = NumSum + Val(NumStr)
End If
Next cell
MsgBox "Sum of numbers is: " & NumSum, vbInformation, "Kutools for Excel"
End Sub2. To run the code, click anywhere inside the macro and press F5, or click the 
Run button above the code editor window. A dialog box will appear asking you to select the range containing numbers with units. The script will automatically extract the leading numeric value from each text cell and sum all results, then display the sum in a pop-up box.
Tips and usage notes:
- This macro extracts the numeric characters from the beginning of each string until a non-number is found, meaning it works on most formats like "5kg", "12 lbs", or "20.5 m".
- If the format of numbers is inconsistent or embedded within the string, further customizations may be required.
- You can modify the VBA code to collect or process units separately if desired.
- To avoid loss of original data, always use macros on a copy or backup of your worksheet.
- Requires that macro functionality is enabled in your Excel settings.
This VBA solution is versatile when dealing with variations in your data, and a considerable time-saver when applied to large datasets.
Use Power Query to Extract and Sum Numbers with Units
Power Query, Excel's built-in data transformation tool, is ideal for processing larger or more complex datasets containing numbers with units.
It works especially well when data structures are inconsistent or when automation is needed for recurring tasks.
1. Select the range containing your data (including numbers with units), then go to the Data tab and click From Table/Range. If prompted, ensure your data has headers.
2. In Power Query Editor, select the column with your text values. Use the Add Column tab > Extract > Text Before Delimiter or Text Between Delimiters.
Choose based on your data format:
- Use a space delimiter if the data is like "12 kg"
- Use a letter-based delimiter if it's "12kg"
3. Change the datatype of the new column to Decimal Number or Whole Number as needed by right-clicking its header and selecting Change Type.
4. (Optional) If you want to sum by unit, further split off the unit with Extract Text After Delimiter, then Group By (on the Home tab) and select Sum for the new numeric field.
5. Click Close & Load to import the processed data and sums directly into Excel as a new sheet or table.
Advantages:
- Handles complex, inconsistent, or bulk data cleanly and quickly.
- Steps can be saved and reapplied to future or updated data—making this approach highly repeatable.
- You can customize transformation logic beyond simple extraction, including handling prefixes, suffixes, decimals, and groupings by different units.
Limitations:
- There is a learning curve for users new to Power Query.
- Best applied to structured data or when working with tables rather than direct ad-hoc cell entries.
Troubleshooting and Suggestions:
- For best results, always review extracted or converted numbers for anomalies, especially if the format of units or numbers varies widely.
- Back up your source data before any bulk actions (including with Kutools and VBA).
- When using formulas, double-check for hidden spaces or typos in data and units.
- If formulas fail or errors are returned (such as
#VALUE!), verify that all referenced cells contain expected formats, and adjust range or unit criteria accordingly. - When using VBA, ensure macros are enabled, and save before running for easier recovery.
- Power Query transformation steps can be adjusted after preview via the Applied Steps pane in case your data has changed.
Relative Articles:
- How to combine multiple cells with leading zeros into a cell in Excel?
- How to combine ranges from different worksheets/workbook into one?
- How to quickly merge rows based on one column value then do some calculations in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in