Sum a range of number end with “k”

Does it sound familiar to you?

Excel Tip - Sum with Text

If you are having this kind of issue all the time, you may want to continue reading this as I am going to show you three different approaches to fix the problem.

  1. Non-Formula approach
  2. Formula approach
  3. Fix the root approach

1. Non-Formula approach (if you can change the original data)

Excel Tip - Sum with Text 1

  • Select the range (B2:B5) in the example
  • Press Ctrl H to open “Find and Replace”
  • Find what: k
  • Replace with:  (nothing; just leave it blank)
  • Replace All
  • Now all “k” are removed and your data returns to real number, i.e. SUM should work as you expect.

Excel Tip - Sum with Text 2

Just to remind to put “(k)” in the header label so that you readers know the figures are in thousand.

2. Formula approach (if you prefer not to change original data at all)

2.1) By using helper column

Excel Tip - Sum with Text 3.0

  • In C2, input the following formula and then copy down.

SUBSTITUTE(text, old_text, new_text, [instance_num])

It simply means “In the text,  look for the old text we specify and then substitute it with the new text we want.”  In our example, it looks into “1.5k“, looks for “k“, and replaces it with nothing.  It returns “1.5” as a result.

SUBSTITUTE always returns text, the latter part of the formula “+0” converts the result (number stored as text) to a real number.

Now the numbers are ready for SUM. 🙂

2.2) By using array formula

I heard that, you don’t want a helper column; and you don’t want to alter the original data either.  Here we go!

Excel Tip - Sum with Text 3

In B6, input the following formula


The SUBSTITUTE function performs exactly the same as illustrated in helper column.  Instead of feeding a single cell reference, a range (B2:B5) is fed into it.

It returns an array of {“1.5” ; “2.3” ; “4.0” ; “12.8”}.

+0 converts the array into a number {1.5;2.3;4;12.8}

SUMPRODUCT then do the simple job to add the values {1.5;2.3;4;12.8} in the array, giving 20.6 as a result.

The final step is to concatenate the “k” to the number for consistent presentation. However this act converts the final result back to text.


As SUMPRODUCT accepts array, it is used here for those who is afraid of Ctrl Shift Enter.  You may replace SUMPRODUCT with SUM; but you have to confirm the formula by Ctrl Shift Enter.

3. Fix the root approach (my preferred approach)

To prevent all these unnecessary tedious steps from happening, call the person who prepares the data, teach him/her how to format numbers into thousand in a proper way.

Don’t you think this is the best approach? 🙂

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips, Formula and tagged , , , . Bookmark the permalink.

5 Responses to Sum a range of number end with “k”

  1. ywwp says:

    Go to number format, and in custom, update 00 k, and then it works fine… the cell shows value with k appended. but do make the calculation where needed.


  2. Jim says:

    your “consistent formatting” by using &”k” is not quite there if the answer is a whole number (ie it would not end in “.0k”)
    to avoid this, use TEXT(formula,”0.0k”) around your formula

    but I wholeheartedly agree with you: option 3 is by far the best (‘cos next time, you’ll get a mixture of “k” and “K” – to which the even-more-cumbersome solution is to add LOWER(…) around your range before SUBSTITUTE)

    Muggles! – don’t you just love ’em?


    • MF says:

      Hi Jim,
      You are right about the decimal issue. Thanks for your suggestion for using TEXT which works great in the example.
      Can’t agree more with you on the “k” and “K” issue. It could be even worse in real life – with a mixture of “nothing”, “K” and “M”. @_@


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Alluring Analytics

A Power BI Creator Blog


Work smarter by Mastering Functions in Excel

roads bel travelled

Exploring open roads without breaking the bank


Work smarter by Mastering Functions in Excel


Your next stop in mastering Power Query and Power BI

ExcelUser Blog

Work smarter by Mastering Functions in Excel


Leading Consultants on Power BI and the Microsoft Data Platform

Excel Esquire

Helping lawyers make the most of Microsoft Excel

ASAP Utilities for Excel – Blog

Work smarter by Mastering Functions in Excel

Analyst Cave

Work smarter by Mastering Functions in Excel

The Excelguru Blog

More geeky stuff from the author of

The Spreadsheet Page

Work smarter by Mastering Functions in Excel

Peltier Tech Blog

Peltier Tech Excel Charts and Programming Blog


Turn your data into opportunity

Let's Talk Excel

Q&A about Excel


Doug Glancy's Excel Site

Option Explicit VBA

A blog of Excel, Dashboards, Visual Basic for Applications, Data Analysis, Operations Research, and Visualizations.


Advanced Excel Techniques with XOR LX

Excel Unplugged

Collection of Excel Know How and little Excel Tips and Tricks

Excel and UDF Performance Stuff

Charles Williams on 'Making Excel go Faster'

Contextures Blog

Save Time and Money in Business

My Online Training Hub

Work smarter by Mastering Functions in Excel

Bacon Bits

Work smarter by Mastering Functions in Excel - Learn Excel, Power BI & Charting Online

Fresh Excel Tips, Power BI, Power Pivot, Power Query, Tricks, Charts, Tutorials, Downloads, Dashboards and Visualization Showcase for your Inspiration and Productivity

%d bloggers like this: