CODEWITHSUNDEEP
excelexcel-formulasumexcel-2010excel-2007
user3292755
user3292755

Reputation: 413

How to sum in excel with characters

I want to Sum in excel with special characters in it. Here is a portion of the data

30.00A
0.00A
0.00A
0.00A
0.00A
0.00A
0.00A
0.00A
0.00A
50.00B
0.00A
0.00B
100.00A
.
.
.

For A stands for a thousand, or times 1000. And B for Million, or times 1.000.000. I have thousands of data like this, so i cannot manually change the A into the real number and also for B. Are there any ways i can do this? thank you so much

Upvotes: 1

Views: 1572

Answers (2)

user2038587
user2038587

Reputation: 21

You can use sumproduct function in excel try this function

=SUMPRODUCT (array1, [array2], ...)

Upvotes: 1

user4039065
user4039065

Reputation:

A SUMPRODUCT function can provide half of the array processing; CSE¹ can provide the rest.

In an unused cell as an array formula¹,

 =SUMPRODUCT(CHOOSE(CODE(RIGHT(A2:A14))-64, 10^3, 10^6, 10^9), --LEFT(A2:A14, LEN(A2:A14)-1))

I've added one more 'suffix' to your system to allow for billions.

        sum_choose

There are better ways of contracting number display with custom number formats than adding custom alphabetic suffixes. Consider putting actual numbers into the cells and using a custom number format of,

[Color13][>999999]0.00,,\B;[Color9][>999]0.00,\A;[Color10]0.00

        sum_choose_numerber_format

Note that the numbers in column B are right-aligned in their respective cells the way numbers are supposed to be. The raw underlying numbers are the actual values with no rounding; they only look like your special numbering system and as such they can be used in any calculation without modification.

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Upvotes: 2

Related Questions