cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

COUNTIF in SharePointList

The full ask is to have a complete count of how many YESs, N/As, and NOs in each row.   I've tried a couple COUNTIF versions but keep getting errors I can't share the full column names and data, BUT here's my excel example of what I'd like: Help???

 

 

OneTwoThreeFourTotal YesTotal NosTotal NAs
YESYESYESYES   
NOYESYESYES   
YESN/AYESYES   
NYESYESYES   
NOYESN/AYES   
N/ANOYESYES   

 

 

 

9 REPLIES 9
Super User III
Super User III

Hi @EEdwards ,

The YES total would be (repeat for the others)

CountRows(
   Filter(
      YourTableName,
      One="YES"
   )
)+
CountRows(
   Filter(
      YourTableName,
      Two="YES"
   )
)+
CountRows(
   Filter(
      YourTableName,
      Three="YES"
   )
)+
CountRows(
   Filter(
      YourTableName,
      Four="YES"
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Community Support
Community Support

Hi@EEdwards,

Based on the issue that you mentioned, do you want to count the Total YES/NO/NA in a row?

Could you please share a bit more about the scenario, do you have a Gallery or a Form to do this calculation?

In my scenario, I have generated an app directly based on a Table from the Excel as below:05.png

This app has three screens by default, BrowseScreen for a Gallery to display all the items, DeatilScreen for a detail form to display the detailed information, EditScreen for an edit form to create or edit the certain item.

In the Gallery, you could inset Labels as many as you want to display the column data. In my scenario, I insert 3Labels to display the Total YES/NO/NA.

Set the Text property of the Label corresponding to the 'Total YES' as below:

Sum(If(ThisItem.One="YES",1,0),If(ThisItem.Two="YES",1,0),If(ThisItem.Three="YES",1,0),If(ThisItem.Four="YES",1,0))

Set the Text property of the Label corresponding to the 'Total NO' as below:

Sum(If(ThisItem.One="NO",1,0),If(ThisItem.Two="NO",1,0),If(ThisItem.Three="NO",1,0),If(ThisItem.Four="NO",1,0))

Set the Text property of the Label corresponding to the 'Total NA' as below:

Sum(If(ThisItem.One="N/A",1,0),If(ThisItem.Two="N/A",1,0),If(ThisItem.Three="N/A",1,0),If(ThisItem.Four="N/A",1,0))

What's more, if you want to save this total calculation into the Excel table, you could insert Button and set the OnSelect property as below:

Patch(Table1,ThisItem,{'Total Yes':Label2.Text,'Total No':Label3.Text,'Total NA':Label4.Text})

Note: Every clicking on the button in the current row of the gallery will save the total calculation result to the corresponding row in the Excel table.

06.png

Hope it could help.

Best Regards,

Qi

 

Tried it above and keep getting Sorry, something went wrong - Formula contains a syntax error or is not supported. 

 

Here's the full messy names of just two of the columns to try and jumpstart it that way:::

More> choosing calculated formula with output of a number

this is what I tried once in Add Column> choosing calculated and data type returned of a number. 

 

=CountRows(Filter(SiteAssessmentWorkPermit,ProperMonitoringTypeIdentifiedonWorkPemit="YES" ))+CountRows(Filter(SiteAssessmentWorkPermit,ConfinedSpaceSafetymeasuresSpecifiedOnPermit="YES"))

 

 

EEdwards_1-1605717099639.pngEEdwards_2-1605717165699.png

 

This could potentially work for this view to give other some information, but the aim is to have this all push into a PowerBI for additional data analysis.  

 

As for me following what you have listed above, I understand the concept of inserting Labels, but I have no idea where labels are , what view you have displayed below or how you start.  can you please fill in the blank for me?

So far I'm looking at the list in gallery view....

@EEdwards ,

So you are trying to get your result directly in calculated columns in Sharepoint. Here is my proposal for Total Yes:

=IF([One]="Yes",1,0)+IF([Two]="Yes",1,0)+IF([Three]="Yes",1,0)+IF([Four]="Yes",1,0)

Of course, the other columns will have a similar structure.

From my point of view it is wise and safe to do this calculation directly in PowerBi ->PowerQuery.

 

Hope it helps !

Community Support
Community Support

Hi@EEdwards,

Could you please read my reply again?

Could you please tell me what exactly is your data source, an SP list or ac Excel table?

You said that you have an Excel table as the data source, so I advise you to generate an app directly based on the data source. 

09.png

 

10.png

Then you could create an app, please check the following steps to inset Labels.

11.png

And you should put the formula I provided before within the Text property of the Label.

I create a demo app, you can check it for details. Here are some guide how you can open this demo app on your device.

12.png13.png

Ps: If you have an SP list as your data source, please do make sure that every column type is a Text type in order that all the formula I provided you could use.

Hope it could help.

Best Regards,

Qi

 

alright -- caught up and understand, but it seems the information is still not pulling or getting understood for any of the text after the ampersand.  my guess is I checked the data type and unfortunately it is listed as COMPLEX and not text, therefore that would be the why the "YES" and second portion may not be working? 

 

EEdwards_0-1605824038548.png

 

unfortunately this one errors out in the powerBI option:  

 

EEdwards_1-1605824584929.png

 

Community Support
Community Support

Hi@EEdwards,

Could you please answer my questions firstly in order that I could capture the key?

A complex column type, is it a Choice or other else?

Please do provide more details for that I could not help if I'm the only one imagining things here.

It is not enough to provide a screenshot about a formula, the error message is the most important.

So please do answer the following question:

  1. What is the data type of the aimed column, a Choice type?

If it is a Choice type, please replace the ThisItem.ColumnName with ThisItem.ColumnName.Value

Hope it could help.

Best Regards,

Qi

 

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (22,706)