Excel -Count lines with condition

Hey,

I have an excel list with two columns, one containing the customer, the other whether the customer receives a newsletter.
Bildschirmfoto 2020-03-20 um 12.27.15

I would now like to find out how many customers receive a newsletter, so I would like to count how often there is a yes in the Newsletter column.
Can someone explain how it works?

Help would be great.

1 Like

Fine to know the count of customer with newsletter as Yes then once after reading the data from excel with read range saving it in a datatable named dt
Use a assign activity like this
int_count = dt.AsEnumerable().Select(Function(a) a.Field(of String)(“Newsletter”).ToString.ToUpper.Equals(“YES”)).Count

This will give us count value to a int32 variable named int_count

Cheers @RoboticApprentice

2 Likes

Hey @Palaniyappan

Thanks for your quick reply.

I think i did exactly what you said but i get this error can you help me?

best regards

@RoboticApprentice Can you Check this Statement :
int_count = dt.AsEnumerable().Select(Function(a) a.Field(of String)(“Newsletter”).ToString.ToUpper.Equals(“YES”)).ToArray.Count

Thanks for your quick reply.

I get the same error :confused:

@RoboticApprentice I guess The Datatable what you are using is Empty, Hence you get that error :sweat_smile:

1 Like

Hups I forgot to set the output variable in the read range function :smiley:

But now I got another problem.
He now gives me the number of all columns and not just those that contain yes
Do you know why ?

@RoboticApprentice That Depends on the Values , Hence Try this Code :sweat_smile: int_count = dt.AsEnumerable().Select(Function(a) a(“Newsletter”).ToString.ToUpper.Trim.Equals(“YES”)).ToArray.Count

Also if it doesn’t work, Is it Possible to Show us Your Excel File ?

Stil the same problem. I always get the answer “9” but the correct answer should be “5”.

this is the excel file I’m working with

Test.xlsx (24.3 KB)

@RoboticApprentice Check with this :
dt.AsEnumerable().Where(Function(a) a(“Newsletter”).ToString.ToUpper.Trim.Equals(“YES”)).ToArray.Count

1 Like

Worked thank you :slight_smile:

1 Like

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