COUNTIF in SharePointList

[EEdwards]

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???

One	Two	Three	Four	Total Yes	Total Nos	Total NAs
YES	YES	YES	YES
NO	YES	YES	YES
YES	N/A	YES	YES
N	YES	YES	YES
NO	YES	N/A	YES
N/A	NO	YES	YES

[WarrenBelz]

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.

[v-qiaqi-msft]

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:

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.

Hope it could help.

Best Regards,

Qi

[EEdwards]

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]

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....

[gabibalaban]

@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 !

[v-qiaqi-msft]

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. 

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

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.

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

[EEdwards]

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]

unfortunately this one errors out in the powerBI option:  

[v-qiaqi-msft]

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