# Sum up concatenation values

#### default_name

##### Board Regular
Hello,

Is there any way to sum up the numeric values of concatenations?

Example below.
The data displayed in cells A2:D2 are a result of some concatenations.
In cell E2 I would like to be able to sum up those concatenated values.
In this case, E2 should come up as 137 days.

Stipulations:
I cannot remove those concatenations in columns A:D.
I cannot use macros/VBA for this.

Is these any way this can be done? Thanks

ABCDE
1Step 1Step 2Step 3Step 4Total Days
25 days28 days103 days1 day
3

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### gaz_chops

##### Well-known Member
Try

=SUMPRODUCT((LEFT(A2:D2,FIND(" ",A2:D2)))*1)

#### default_name

##### Board Regular
Try

=SUMPRODUCT((LEFT(A2:D2,FIND(" ",A2:D2)))*1)

View attachment 15713
Thanks for the quick response.
I tried entering in that formula but it resulted in a #VALUE!

Perhaps it may help if I describe the formulas being used in the concatenated cells.
This is what is in cell A2.
Rich (BB code):
``=CONCATENATE(COUNTIF(A5:A100,"*y*"),CHAR(10),"days")``

#### lrobbo314

##### Well-known Member
Here is a way using Power Query.

Book1
ABCDE
1Step 1Step 2Step 3Step 4Total
25 days28 days103 days1 day137
Sheet3

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Total = Table.AddColumn(Source, "Total", each List.Sum(List.Transform(Record.FieldValues(_), each Int64.From(Text.Split(_," "){0}))))
in
Total``````

#### gaz_chops

##### Well-known Member

Hmm appears to be the fact that you are using CHAR(10)!

#### gaz_chops

##### Well-known Member
Try

=SUMPRODUCT(--(LEFT(A4:D4,FIND("d",A4:D4)-2)))

#### Fluff

##### MrExcel MVP, Moderator

+Fluff New.xlsm
ABCDE
1Step 1Step 2Step 3Step 4Total Days
25 days28 days103 days1 day137
Data
Cell Formulas
RangeFormula
E2E2=SUMPRODUCT(--(LEFT(A2:D2,FIND(CHAR(10),A2:D2)-1)))

#### default_name

##### Board Regular
Try

=SUMPRODUCT(--(LEFT(A4:D4,FIND("d",A4:D4)-2)))
+Fluff New.xlsm
ABCDE
1Step 1Step 2Step 3Step 4Total Days
25 days28 days103 days1 day137
Data
Cell Formulas
RangeFormula
E2E2=SUMPRODUCT(--(LEFT(A2:D2,FIND(CHAR(10),A2:D2)-1)))
I tried both of these formulas and they both result in a #VALUE!

Columns B:D contain options for both days and day (in the case of "1 day") like this:
Here is what is in B2.
Rich (BB code):
``=IF((COUNTIF(B5:B100,"*y*")=1),CONCATENATE(COUNTIF(B5:B100,"*y*"),CHAR(10),"day"),CONCATENATE(COUNTIF(B5:B100,"*y*"),CHAR(10),"days"))``
Could this be causing the issue? For day vs days consistency I can also make A2 look like B2:D2.

#### default_name

##### Board Regular
SOLVED IT!
I unmerged the columns and it all works perfectly.

Thanks Fluff and gaz_chops!
Also thank you to lrobbo314 for providing a vba option if it ever needed it.

You guys are awesome!

#### Fluff

##### MrExcel MVP, Moderator
Glad you sorted it & thanks for the feedback.
Best advice is NEVER use merged cells.

1,106,625
Messages
5,512,473
Members
408,899
Latest member
cve60069