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(B2,"k","")+0 

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

 =SUMPRODUCT(SUBSTITUTE(B2:B5,"k","")+0)&"k"

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.

Tip:

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.

    Like

  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?

    Like

    • 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”. @_@
      Cheers,

      Like

Comments, suggestions, corrections are welcome.

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

WordPress.com Logo

You are commenting using your WordPress.com 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.

The BIccountant

New Generation Finance - Accounting - Controlling using Microsoft BI stack

Alluring Analytics

A Power BI Creator Blog

roads bel travelled

Exploring open roads without breaking the bank

RADACAD

Work smarter by Mastering Functions in Excel

DataChant

Your next stop in mastering Power Query and Power BI

ExcelUser Blog

Work smarter by Mastering Functions in Excel

P3 Adaptive

Leading Consultants on Power BI and the Microsoft Data Platform

Excel Esquire

Helping lawyers make the most of Microsoft Excel

ASAP Utilities 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 www.excelguru.ca...

The Spreadsheet Page

Work smarter by Mastering Functions in Excel

Peltier Tech

Peltier Technical Services - Excel Charts and Programming

ExcelFort

Turn your data into opportunity

Let's Talk Excel

Q&A about Excel

%d bloggers like this: