# How to do Excel SUM? Help

Hello, I’d like to perform a SUM function in Excel, but I’m facing a constraint. If there are 3 'P’s and 2 'B’s in a cell, it should be represented as ‘2B3P’. However, when I use CONCAT, it gives me something like ‘2BBB4PPP’. Can you please provide a solution? I’ll supplement my explanation with an image.

@22222222asas

Try this:

`string.join("",dt.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.count & b.Key).toarray)`

2 Likes

@22222222asas

try this

``````string.join("",dt.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.count & b.Key).toarray)
``````

@Dilli_Reddy
Thank you for your response! Can you show me the code?

1. use Build data table and give the output
2.use assign activity

Sequence1.xaml (16.2 KB)

1 Like

@22222222asas

here code
in image

Sequence1.xaml (9.2 KB)

here its xmal

1 Like

@Dilli_Reddy

Thank you for your answer! BUT Data SUM with 2 should result in 4 PLT, but it only shows 3 PLT. Can you please provide a solution for this issue?

@sai_gupta

Thank you for your answer! BUT Data SUM with 2 should result in 4 PLT, but it only shows 3 PLT. Can you please provide a solution for this issue?

In UiPath workflow using Invoke Code or Assign activities for the given VB.NET code:

• Input DataTable: inputTable
• Please change the column names
• Grouping by Column1 and calculating the sum of Column2 for each group
• Sorting the result in ascending order based on the sum of Column2
• Concatenating the sorted results
``````Dim groupedData = inputTable.AsEnumerable().
GroupBy(Function(row) row.Field(Of String)("Column1")).
Select(Function(group) New With {
.Key = group.Key,
.SumColumn2 = group.Sum(Function(row) row.Field(Of Integer)("Column2"))
})

Dim sortedData = groupedData.OrderBy(Function(entry) entry.SumColumn2)

Dim result As String = String.Join("", sortedData.Select(Function(entry) \$"{entry.SumColumn2}{entry.Key}"))
``````

Sample file is here:
Main.xaml (10.3 KB)

1 Like

@aanandsanraj
Thank you so much for the response. I’m truly sorry to bother you, but could you please also explain how to do this using READ RANGE to read Excel?

@22222222asas

``````string.join("",input_dt.AsEnumerable.GroupBy(Function(a) tuple.Create(a(0).ToString,a(1).ToString)).Select(Function(b) b.count & b.key.item1).toarray)
``````

Try this once

1 Like
• Read Range activity output the result to a variable, let’s call it dtReadResult.
• In the Invoke Code activity, you need to pass the variable dtReadResult to inputTable

1 Like

@aanandsanraj

When proceeding as you suggested, I encounter this error. Is there a solution to resolve it?
error : Invoke Code: Exception has been thrown by the target of an invocation.

@Dilli_Reddy
When I applied the code you provided, I got the following result. Is there any other approach I can try on my end?

I want result = 9P 1

Current Results

@22222222asas

``````string.join("",InputDT.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.key & b.sum(Function(c) cint(c(1).ToString))).toarray)
``````
1 Like

No changes required please see my result below

Result

1 Like

@Dilli_Reddy

Wow, you’re really amazing! Thank you so much. However, what I’m looking for is not P6B4 but rather 6P4B. I’m truly sorry, but could you please make the adjustment just for this part?

I want result = 6P4B

Current Result =

@aanandsanraj

Main.xaml (8.2 KB)
DD.xlsx (9.6 KB)
Thank you for your response. I apologize for my lack of skills! I will upload the file. Please check it for me

string.Join(“”,dt.AsEnumerable.GroupBy(Function(a) a(0).ToString).Select(Function(b) b.key & b.Sum(Function(c) cint(c(1).ToString))).toarray)

1 Like

countB As Int32 = 0
countP As Int32 = 0

For Each row

ElseIf row(“B / P”).ToString.ToUpper.Trim = “B” Then
countB = countB+1
ElseIf row(“B / P”).ToString.ToUpper.Trim = “P” Then
countP = countP+ 1

result As String = countB.ToString + “B” + countP.ToString + “P”

Hope this helps

1 Like