Sum datatable rows except the rows containing a specific value

Help
1

Hi,

I’m trying to sum a column within a datatable but not all the fields. E.g. the datatable consists of column, project name, project number, document type, debit amount and credit amount. I want to sum all the value except those with document type ‘journal’. Also, I’ve tried several formulas posted but I don’t know how to apply the where in the formula.

Any and all help would be appreciated.

Thanks!

2

Hi @shauny is this what you are going for? Main.xaml (14.2 KB) Spreadsheet.xlsx (9.0 KB) by

3

Hi @shauny,

  1. I think firstly, you should take out rows with document type is ‘journal’. so for that, I’ll suggest you to use Filter DataTable activity as shown in below shot.

image

As remove radio button is selected it’ll remove rows which has document type is ‘journal’.
Don’t forget to specify input datatable (dt1) & output datatable (dt2).

  1. Now, you have filtered datatable you can use following query to get sum.

Sum(String) = (dt2.AsEnumerable().Sum(Function(i) Convert.ToDouble(i.Field(Of Double)("colName").ToString)))

5

Hi @shauny,

Are you looking for something like this excel_uipathforum.zip (501.9 KB) ?
The datatable is obtained from a Excel file please unzip and execute the main file only.

6

Thanks for the advice, the sum code is not working. I assigned sum to string but i get the following validation error: “Option strict on disallows implicit conversions from ‘double’ to ‘string’”

assign
assign921×172 4.28 KB
That’s what I have in the assign.

7

Not exactly, I want the sum of the all the rows in a column except the rows that have a document type of ‘journal entry.’ Your answer would be subtracting the values in the debit and credit column of each row. I want to add all the dollar values in the debit column.

9

Try this:
dt.AsEnumerable().Where(function(x) Not x(“Document Type”).ToString.Equals(“journal”)).Sum(function(y) If(Double.TryParse(y(“Credit Amount”).ToString,1), Double.Parse(y(“Credit Amount”).ToString), 0)).ToString

1 Like
11

It worked!! It was my mistake I put the sum in the same column. Thank you so much!!

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.