Count in Excel how many entries are present

[PatrickSched]

Hello all together

I would like to count via Power Automate in an Excel table how many entries of a value are present in a column.

For example: how often is "123456" present in column "H".

Thank you for your help 🙂 

[ManishSolanki]

Hi @PatrickSched 

Assuming your excel is on onedrive and "EmployeeId" is the header name for col H. You can get the rows by using "List rows of a table" action:

and using below expression, you can get the count of rows:

length(outputs('List_rows_present_in_a_table')?['body/value'])

Please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[PatrickSched]

Hi @ManishSolanki 

perfect, it works. 

Can I specify multiple filter options in Filter query?

For example:

Filter me in column "H" all entries with "123456" and in column "A" all entries where the cell is empty.

So it does not work for me and I get an error message

Thank you for your help 🙂 

[ManishSolanki]

Hi @PatrickSched 

The query looks ok but syntactically it is wrong. Only thing you need to do is to replace comma before Status with "and":

<<first part of query will be same>> and Status eq ''

Please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[PatrickSched]

hi @ManishSolanki 

I have entered it as follows but I get an error message

Do you know what I did wrong or what the problem is?

Thank you very much! 🙂

[Srini007]

@ManishSolanki 

You have given Artikelnumber eq the variable

but in Status eq  what? that is missing can you complete the expression

Hope this may help you

Thanks,

Srini

[ManishSolanki]

Hi @PatrickSched 

Please remove comma ',' before and keyword as highlighted below:

Thanks

Please remember to give a 👍 and accept my solution as it will help others in the future.

[PatrickSched]

Hi all together,

When I search for an item number (is a dynamic content) in the "Item number" column, it works.

Now I want to count only the entries in the column "Status" where the column is empty.

This is how the function should be:
Search in column "Item number" for an item number and in column "Status" for cells that are empty.

What is the command?

[PatrickSched]

This is the error message

'Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.'

and this my flow which don't work

what im doing wrong?

[PatrickSched]

Ok, I have another option but again I need your support.

I can also do the whole thing using a SharePoint list.

I have in a column (Title) the number.
In one column I have the date of creation. How can I count only the entries to which the article number fits and the creation (Column "Erstellt") is not older than 21 days ago?

[PatrickSched]

ok, i tried it again in excel, but i don't work. 

Error message: 

"Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.".

Have anyone an idea? 😞 

[RobElliott]

A filter query in the Excel List rows action doesn't support multiple conditions, unlike for example, the SharePoint get items action which does.

[ManishSolanki]

Hi @PatrickSched 

My apologies for sharing an incorrect filter query.

Thanks

[PatrickSched]

Dear all,

Does anyone have an idea how I can query two columns differently?

Best regards

[Srini007]

@PatrickSched 

Check below for your reference

https://powerusers.microsoft.com/t5/General-Power-Automate/Multiple-filter-in-ODATA-in-List-rows-pre...

Hope this may help you

Thanks,

Srini

[ManishSolanki · Accepted solution]

Hi @PatrickSched 

I have a workaround for you. You can use only one filter condition inside "List rows present in a table" action, only for Artikelnumber and erase the second condition (all from "and" to end). Now you can use "Filter array" action to further filter the result obtained from the above action as shown below:

I hope it will resolve your problem 🙂

Thanks

Please remember to give a 👍 and accept my solution as it will help others in the future.

[PatrickSched]

Hi @ManishSolanki 

I have tried the following but I can't get the number of entries I need in Excel as output.

My flow

For "Compose 2", I want to get the number of entries that satisfy both conditions.

So once the article number in the column "Article number". Then second filter at column "Status" all where are on "open".

Where is the error?

Thank you for your help 🙂