CODEWITHSUNDEEP
excelif-statement
user3466246
user3466246

Reputation: 1

How to add numbers in Excel in the same row and separate them by 'suffix'?

I have a list with more than 700 entries (see the picture below).

In row 'B' there are some numbers that have suffix "KM", "KN" or "E".

Is there some formula(or any solution) that can add these numbers but in the same time to separate these 3 suffixes(not to add them together, but to separate "KM", "KN" and "E"), so the solution can be something like:

 1. 345 KM
 2. 220 KN
 3. 560 E

enter image description here

Upvotes: 0

Views: 240

Answers (4)

Sunny Patel
Sunny Patel

Reputation: 8077

It sounds like you want to add each type of suffix together.

Simplest way is to separate the numbers into its own column. You can enter =NUMBERVALUE(LEFT(B1,SEARCH(" ",B1))) in cell C1 and copy down for each row to accomplish this. Then use a SUMIF function to get the results.

  • KM: =SUMIF(B1:B999,"=*KM",C1:C999)
  • KN: =SUMIF(B1:B999,"=*KN",C1:C999)
  • E: =SUMIF(B1:B999,"=*E",C1:C999)

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96773

With data in B1 , in C1 enter:

=--MID(B1,1,FIND(" ",B1)-1)

and copy down. In D1 enter:

=MID(B1,FIND(" ",B1)+1,999)

and copy down

Finally to get the sums, use formulas like:

=SUMPRODUCT((C:C)*(D:D="KM"))

Upvotes: 1

nutsch
nutsch

Reputation: 5962

Use Data \ Text to Columns to split your column based on the space character, then pivot your data and use the Unit (KM, KN, E) as a row field and the number as a data field.

Upvotes: 1

SS781
SS781

Reputation: 2649

The below formulae should be able to split the numbers and the suffixes found in column B:

=LEFT(B1, 0, FIND(" ", B1))
=LEFT(B1, FIND(" ", B1)+1, 9999)

If you wanted to avoid VBA, you could use DSUM. I've never actually used it, but I know it can be useful for this type of thing. Hopefully that points you in the right direction!

Upvotes: 1

Related Questions