Conditional Logic

Excel formula for Check if all cells in a range match

Tell whether every cell in a range meets a condition.

Copy this formula

=IF(COUNTIF(B2:B10, "Yes")=COUNTA(B2:B10), "All", "Not all")

How the formula works

Compare the count of matching cells to the count of filled cells — if they're equal, every cell matches. It's a quick all-pass / all-complete check for checklists and approvals.

Examples

=IF(COUNTIF(Tasks, "Done")=ROWS(Tasks), "Complete", "Pending")→Pending

All tasks done?

Other ways to do this

With AND over conditions

=AND(B2:B10>0)

TRUE only if every cell is positive (array).

FAQ

Why use COUNTA instead of a fixed count?

COUNTA adapts if some rows are blank, so the check stays correct as the list changes.

Related formulas

Count cells that meet a condition Check if a value exists in a list

Functions used

COUNTIF COUNTA AND

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free