cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RobRoy
Level: Powered On

CountIF with contains or "In"

Hello,

 

I am trying to count the results of a search using the following:

 

CountIf(Events, Event = TextSearchBox1_1.Text)

But the results only count if the search words are an exact match for all the words in the Event field of the Events datasource (SharePoint list). How can I edit this code to count all the results when only one or two keywords match the entire Event? I already have the search working since I see results when only one keyword matches.

 

Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: CountIF with contains or "In"

You can use the Find function to check whether the event contains the word in the text search box:

CountIf(Events, Find(TextSearchBox1_1.Text, Event) > 0)

That will give you the count of the Events records where the Event column contains the text in the search box.

View solution in original post

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: CountIF with contains or "In"

You can use the Lower (or Upper) function on both sides to make the comparison case-insensitive.

CountIf(Events, Find(Lower(TextSearchBox1_1.Text), Lower(Event)) > 0)

Notice that currently those functions (Lower/Upper) are not delegatable to the server (Sharepoint, in your case) yet, which means that they will only be applied in a local copy of the list. If your list is small (less than 500 elements, IIRC), then it will not be a problem, but if it's larger than that, then this workaround will not work at this moment.

View solution in original post

4 REPLIES 4
PowerApps Staff CarlosFigueira
PowerApps Staff

Re: CountIF with contains or "In"

You can use the Find function to check whether the event contains the word in the text search box:

CountIf(Events, Find(TextSearchBox1_1.Text, Event) > 0)

That will give you the count of the Events records where the Event column contains the text in the search box.

View solution in original post

RobRoy
Level: Powered On

Re: CountIF with contains or "In"

That works, thank you! Although I notice it is case-sensitive. Any way to make it case-insensitive?

PowerApps Staff CarlosFigueira
PowerApps Staff

Re: CountIF with contains or "In"

You can use the Lower (or Upper) function on both sides to make the comparison case-insensitive.

CountIf(Events, Find(Lower(TextSearchBox1_1.Text), Lower(Event)) > 0)

Notice that currently those functions (Lower/Upper) are not delegatable to the server (Sharepoint, in your case) yet, which means that they will only be applied in a local copy of the list. If your list is small (less than 500 elements, IIRC), then it will not be a problem, but if it's larger than that, then this workaround will not work at this moment.

View solution in original post

RobRoy
Level: Powered On

Re: CountIF with contains or "In"

Awesome, thanks again.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,602)