Formula Errors

How to Count Checked Yes/No Boxes in a SharePoint Calculated Column

Guest Writer Oct 12, 2026 870 views

Question details

The user needs to calculate the total number of checked Yes/No boxes across multiple columns within a single SharePoint list item.

How to Count Checked Yes/No Boxes in a SharePoint Calculated Column

Product: SharePoint
Device & OS: not provided
Scenario: Setting up a calculated column to convert multiple Boolean (Yes/No) fields into numeric values and sum them for a total count.
Observed behavior: A calculated column is created using multiple IF functions to evaluate each Boolean field as 1 if true (checked) and 0 if false (unchecked).

Before you start

Ensure you have the exact display names of all the Yes/No columns you intend to count, paying special attention to any spaces in the column names.

Solution 1Recommended

Use IF Functions to Sum Boolean Values

Use a combination of IF functions to manually evaluate each Yes/No column, returning a 1 for 'Yes' and adding them together.

SharePoint does not automatically sum Boolean values using standard COUNT or SUM functions. Instead, you must explicitly convert each checked box ('Yes') to a 1 and each unchecked box ('No') to a 0.

Open List Settings

Navigate to your SharePoint list, click the Gear icon in the top right corner, and select 'List settings'.

Create a New Column

Scroll down to the Columns section and click on 'Create column'.

Configure the Calculated Column

Name your column (e.g., 'Total Checked') and select 'Calculated (calculation based on other columns)' as the data type.

Enter the Formula

In the Formula box, enter your calculation. For example: =IF([Test1],1,0)+IF([Test2],1,0)+IF([Test3],1,0)+IF([Test4],1,0). Make sure to replace Test1 through Test4 with your actual Yes/No column names.

Save the Column

Set 'The data type returned from this formula is' to 'Number', then click 'OK' to apply the changes.

Handling Spaces in Column Names: If any of your column names contain spaces, you must enclose them in square brackets within the formula, for example: IF([My First Column],1,0).

Free Microsoft Office alternative

Analyze SharePoint Data Offline with WPS Office

While SharePoint is excellent for online list management, analyzing complex datasets or performing advanced formula calculations is often faster in a dedicated spreadsheet tool. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office, allowing you to easily manage exported SharePoint data.

1. Export List Data: In SharePoint, use the 'Export to Excel' feature to download your list as a .csv or .iqy file.
2. Open in WPS Spreadsheet: Launch WPS Office Free and open the downloaded file directly within the Spreadsheet module.
3. Perform Calculations: Use standard Excel formulas to analyze your Yes/No data offline effortlessly without complex nested IF limits.

Fully compatible with Microsoft Excel (.xlsx) formats for seamless data transition.Easily process exported SharePoint list data using familiar spreadsheet formulas like COUNTIF.Lightweight installation with blazing-fast performance on any device.Free alternative with a highly familiar user interface for a zero-learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SharePoint calculated column showing a syntax error?

Syntax errors typically occur if you miss a comma or parenthesis in the formula, or if you forget to use square brackets around column names that contain spaces (e.g., [Check Box 1]). Double-check your formula for any typos.

Can I use the COUNT function instead of IF to count checked boxes in SharePoint?

No, the standard COUNT or COUNTIF functions do not work as expected with Boolean (Yes/No) fields in SharePoint calculated columns. Stringing together multiple IF statements is the recommended and most reliable method.

How do I return a blank value instead of a 0 if no boxes are checked?

You can wrap your sum calculation in an outer IF statement that checks if the total is greater than 0. For example: =IF((IF([Col1],1,0)+IF([Col2],1,0))>0, IF([Col1],1,0)+IF([Col2],1,0), "").

Excel & Data Problems