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

CountIf function with SharePoint list.

Hello everyone,

 

I have a list (37 items currently) with actions that have a status of 'Open' or 'Closed'.

 

I'm trying to use a CountIf formula on that list via powerapps on a browse gallery to display the number of open actions on that row. 

 

e.g. Row 1 has 4 actions and two of them are open so I want the returned value of the CountIf function to be 2 on that item.

 

Below is my current formula that works to a degree.

 

CountIf('WALK Register',ThisItem.'Status 1'.Value = "Open")

 

This works but it will count all true values of that column which is currently showing 37. (So each row that has a true value under status will count all rows regardless of their value).

 

I'm under the pump and don't have much time to troubleshoot what I believe to be a simple formula so any help would be great.

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: CountIf function with SharePoint list.

Hi @Clint123 ,

The CountIf function is used to count records that meet the formula not the fields.

Could you tell me the data type of state column?

I assume that's a choice column.

I suggest you try this formula:

CountIf(Split(Concatenate(ThisItem.status1,",",ThisItem.status2,",",ThisItem.status3,",",ThisItem.status4),","),Result="Open")

 

Best regards,

Community Support Team _ Phoebe Liu

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Super User
Super User

Re: CountIf function with SharePoint list.

Hi @Clint123 

I would remove the reference to ThisItem in your formula and see whether or not that returns your desired result.

 

CountIf('WALK Register', 'Status 1'.Value = "Open")

Hope that helps.

Clint123
Level: Powered On

Re: CountIf function with SharePoint list.

Hello @timl 

 

That is getting me somewhere. Now it only shows open values but still shows it on every list. A picture of this is below.

 

Untitled3.png

I'm trying to show the Open values for 'each' list. 

 

Hope this is a simple work around. Thank you for your help.

Super User
Super User

Re: CountIf function with SharePoint list.

Hi @Clint123 

I don't think I fully understand how your data structured. In the gallery that's shown in your screenshot, do you have a sub-gallery that displays the 4 items for each item in your main gallery? If so, what formulas are you using?

Community Support Team
Community Support Team

Re: CountIf function with SharePoint list.

Hi @Clint123 ,

The CountIf function is used to count records that meet the formula not the fields.

Could you tell me the data type of state column?

I assume that's a choice column.

I suggest you try this formula:

CountIf(Split(Concatenate(ThisItem.status1,",",ThisItem.status2,",",ThisItem.status3,",",ThisItem.status4),","),Result="Open")

 

Best regards,

Community Support Team _ Phoebe Liu

 

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User
Super User

Re: CountIf function with SharePoint list.

@Clint123 

 

As you want to get the count of a filter inside a subitem, you need to apply the countif on SubItem, rather than applying it to the data source. Can you please try out the below mentioned formula, this will get you the results for each gallery item corresponding to its sub gallery:
 
Expression:
CountIf(ThisItem.'Status 1',Value = "Open")
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Clint123
Level: Powered On

Re: CountIf function with SharePoint list.

Hello @v-yutliu-msft,

 

You're a lifesaver! This is exactly what I was after. Tweaked the formula slightly but the below worked a charm.

 

CountIf(Split(Concatenate(ThisItem.'Status 1'.Value,",",ThisItem.'Status 2'.Value,",",ThisItem.'Status 3'.Value,","),","),Result="Open")

 

Thank you @yashag2255 & @timl for your excellent minds too. I appreciate you all.

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 282 members 3,871 guests
Please welcome our newest community members: