Total count based on a condition

To check if the column1 in excel has value, if it has then move to the column2 and take the total count until column1 has value.

Hey @ppr , @Palaniyappan any idea ?

Hi @Ray_Sha1

Can u elaborate ur query well?

Hi @Ray_Sha1 Can you please post the requirement with Excel snapshot.

Using Read range specify the column to read, datatable.rows.count will give you the row count, based on that value you can sum up row1 till last row count.

Hi @NIVED_NAMBIAR @Saranyajk @suyog_patil

Attaching a excel screenshot
If Id has value, then check the val column, and get the count.
The output that I want is
a = 2
b = 1
c = 4

For clarity I’ve colour coded them.

Thanks in advance!

First read the id value and and corresponding column, check whether next row is empty, if so check to match the column value with prev one, also keep incrementin the counter.

when the row(id) value change it has to clear the row id value and assign b, continue doing this.

Hi @Ray_Sha1
You can try like this

  1. Read the excel file using read range and store in dt1

  2. then loop through each row of dt1 using for each row activity

inside the for each row do the following:

a.  use assign activity to get the sum  of the val cell for each ID, where sum is INT32 variable
                           sum = Split(row("val"),Environment.NewLine).Sum(Function(e) Cint(e))
b. Then using writeline/Message box u can display the value as ,   row("ID").ToString+" : "+ sum.ToString 


Nived N

Happy Automation

Hey @Saranyajk,
Tried that, having issues with checking the next row is empty.

Getting error in the value part of the assign.

Hi @Ray_Sha1 Created one flow with an approach of Strings manipulation to get the desired output. See the workflow if it fits the requirement and try to implement in your project. Mark it as solution if it resolves your question.

Below is snapshot of Input/Output file post Flow Execution (16.6 KB)

Hi @suyog_patil ,

There’s a error in your code.
Trying to implement it.
Thanks (29.1 KB)

Please try this and tell me if it works

Hey @Saranyajk

It works. Only change required is that instead of the sum, we must give count.

1 Like

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