Function Problems

How to Return YES When All Excel Checkboxes Are Checked

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to display the word YES in a specific cell only if all designated form-control checkboxes in the worksheet are selected, and NO if any are cleared.

Product: Excel
Device & OS: not provided
Scenario: Evaluating the state of multiple form-control checkboxes simultaneously to trigger a conditional output based on whether every single box is checked.
Observed behavior: Dynamically outputting a YES or NO string based on whether every single linked cell registers a TRUE value.

Before you start

Ensure that the Developer tab is enabled in your ribbon so you can insert form-control checkboxes, and verify that you have enough empty cells to link each checkbox individually.

Solution 1Recommended

Use IF and COUNTIF Formulas to Evaluate Linked Checkboxes

Link each checkbox to a distinct cell (which will return TRUE or FALSE) and use a combined formula to evaluate if all linked cells are TRUE.

Form-control checkboxes in Excel cannot be evaluated directly by a formula. They must first be linked to a cell. When the checkbox is checked, its linked cell displays TRUE; when cleared, it displays FALSE.

By counting the number of TRUE values in the linked cell range and comparing it to the total number of checkboxes, you can conditionally output YES or NO.

Insert Form-Control Checkboxes

Go to the Developer tab, click 'Insert', and select the Check Box under Form Controls to draw your checkboxes on the worksheet.

Link Checkboxes to Cells

Right-click the first checkbox, select 'Format Control', and navigate to the 'Control' tab. In the 'Cell link' box, specify a dedicated cell (e.g., S3). Repeat this for the remaining checkboxes, linking them to separate cells like S4 and S5.

Apply the Logic Formula

Select the target cell where you want the YES or NO result to appear (e.g., K30). Enter the formula: =IF(COUNTIF(S3:S5,TRUE)=3,"YES","NO"). Press Enter to apply.

Formula Adjustments: Make sure to replace '3' in the formula with your actual total number of checkboxes, and 'S3:S5' with your actual range of linked cells.

Easily Manage Checkboxes and Formulas in WPS Spreadsheet

WPS Office provides a highly compatible and user-friendly spreadsheet tool where you can seamlessly insert checkboxes and apply complex logical formulas just like in Microsoft Excel.

1. Enable the Developer Tab: Open WPS Spreadsheet, go to the Options menu, and customize the ribbon to ensure the Developer tab is checked and visible.
2. Insert and Link Checkboxes: Under the Developer tab, click Insert > Check Box. Place them on your sheet, right-click to access Format Object, and assign a linked cell for each.
3. Write the Evaluation Formula: Use the exact same =IF(COUNTIF(range,TRUE)=total,"YES","NO") formula to dynamically evaluate your checkboxes.

Fully compatible with Microsoft Excel (.xlsx) formats and form control objects.Easy-to-use Developer tools for quickly inserting and linking checkboxes.Supports all standard logical formulas, including IF, AND, and COUNTIF.Lightweight software that runs smoothly on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use the AND function instead of COUNTIF to check if all checkboxes are TRUE?

Yes, you can use =IF(AND(S3, S4, S5), "YES", "NO"). This method is simpler if you only have a few checkboxes, but COUNTIF is much better and easier to manage for a large, continuous range of linked cells.

Why does my formula return 'NO' even when all checkboxes appear checked?

This usually happens if one or more checkboxes are not correctly linked to the cells referenced in your formula. Right-click each checkbox, select Format Control, and verify that the 'Cell link' address points to the exact cells your COUNTIF formula is evaluating.

Is it possible to hide the TRUE/FALSE text in the linked cells?

Yes. You can hide the text so it does not clutter your spreadsheet. Select the linked cells, press Ctrl+1 to open the Format Cells dialog, choose the Custom category, and type ;;; (three semicolons) as the format type.

Can I apply conditional formatting to the cell displaying YES or NO?

Absolutely. Select the cell containing the IF formula, go to Home > Conditional Formatting > Highlight Cells Rules, and set rules to color the cell green if the text is 'YES' and red if the text is 'NO'.

Excel & Data Problems