SOLVED

COUNTIF alternative in SharePoint

Copper Contributor

Hi,

 

I'm not sure if this is a duplicate query but I am unable to find posts regarding this. I am creating a SharePoint list and the file I am getting the required info from contained IF and COUNTIF to score a field depending on the subfields under it.

 

The formula used in excel was =IF(COUNTIF(C24:C36,"No"),"No","Yes") for the Details field.

 

Per the screenshot below, if one of the subfields (Account, Contact, etc) get a No, then the whole Details field will be marked as No getting an actual score of 0 out of the possible 2.

 

ann_without_e_0-1694497742853.png

 

What is the best way I can execute this on the SharePoint list? I tried COUNTA but it doesn't seem to be working the way I want it (or I may be doing it incorrectly :lol:)

 

Thank you in advance for any assistance :smile:

3 Replies
best response confirmed by ann_without_e (Copper Contributor)
Solution

@ann_without_e 

In SharePoint Online, you can achieve similar functionality to Excel's COUNTIF with calculated columns and SharePoint column formulas. Unfortunately, SharePoint does not provide a direct COUNTIF function like Excel, but you can work around it using other column types and formulas.

For your specific scenario, where you want to check if any of the subfields contain "No" and then determine the value for the "Details" field accordingly, you can use a combination of calculated columns and column formulas. Here is a step-by-step guide on how to do this:

  1. Create Columns:
    • Create a SharePoint column for each subfield (e.g., Account, Contact, etc.), and set the data type as Choice or Single line of text, depending on how you want to input the values ("Yes" or "No").
    • Create a calculated column for the "Details" field. You can name it "DetailsCalc" or something similar.
  2. Define Calculated Column Formula:
    • In the calculated column "DetailsCalc," you can use the IF and OR functions to check the subfields' values and determine the "Details" field's value. The formula might look something like this:

=IF(OR([Account]="No",[Contact]="No",[OtherField]="No",...),"No","Yes")

Replace [Account], [Contact], [OtherField], etc., with the actual column names for your subfields. You should include all the subfields you want to check in the OR statement.

  1. Update Existing Items:
    • If you have existing data in your SharePoint list, you may need to update the calculated column for those items. You can do this by editing the item and saving it without making any changes.
  2. Display "DetailsCalc" Column:
    • Now, you can display the "DetailsCalc" column in your SharePoint list view, and it will show the calculated values based on the subfields' values.

This approach will calculate the "DetailsCalc" column for each item based on the values in the subfields. If any of the subfields contain "No," it will set "DetailsCalc" to "No"; otherwise, it will be set to "Yes."

Please note that this method relies on calculated columns, and the formula is evaluated when an item is created or updated. If you need real-time calculations based on changes to the subfields, you might need to consider using SharePoint Designer workflows or Power Automate flows to automate the process further.The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

@NikolinoDE The IF and OR worked liked a charm, thank you so much! I just did another calculated column to have the numerical value from the DetailsCalc column using IF to get the actual points for Details. 

 

Appreciate the fast response, thanks again! :smile:

 

 

Hi, what would be the equivalent solution for counting the amount of times a specific value (a word) appears in a row? This case seems to be number based.

1 best response

Accepted Solutions
best response confirmed by ann_without_e (Copper Contributor)
Solution

@ann_without_e 

In SharePoint Online, you can achieve similar functionality to Excel's COUNTIF with calculated columns and SharePoint column formulas. Unfortunately, SharePoint does not provide a direct COUNTIF function like Excel, but you can work around it using other column types and formulas.

For your specific scenario, where you want to check if any of the subfields contain "No" and then determine the value for the "Details" field accordingly, you can use a combination of calculated columns and column formulas. Here is a step-by-step guide on how to do this:

  1. Create Columns:
    • Create a SharePoint column for each subfield (e.g., Account, Contact, etc.), and set the data type as Choice or Single line of text, depending on how you want to input the values ("Yes" or "No").
    • Create a calculated column for the "Details" field. You can name it "DetailsCalc" or something similar.
  2. Define Calculated Column Formula:
    • In the calculated column "DetailsCalc," you can use the IF and OR functions to check the subfields' values and determine the "Details" field's value. The formula might look something like this:

=IF(OR([Account]="No",[Contact]="No",[OtherField]="No",...),"No","Yes")

Replace [Account], [Contact], [OtherField], etc., with the actual column names for your subfields. You should include all the subfields you want to check in the OR statement.

  1. Update Existing Items:
    • If you have existing data in your SharePoint list, you may need to update the calculated column for those items. You can do this by editing the item and saving it without making any changes.
  2. Display "DetailsCalc" Column:
    • Now, you can display the "DetailsCalc" column in your SharePoint list view, and it will show the calculated values based on the subfields' values.

This approach will calculate the "DetailsCalc" column for each item based on the values in the subfields. If any of the subfields contain "No," it will set "DetailsCalc" to "No"; otherwise, it will be set to "Yes."

Please note that this method relies on calculated columns, and the formula is evaluated when an item is created or updated. If you need real-time calculations based on changes to the subfields, you might need to consider using SharePoint Designer workflows or Power Automate flows to automate the process further.The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

View solution in original post