Flag Rows Meeting All Conditions (AND)

Excel Formulas › Logical

All versionsAND

Mark the rows that pass every test — in stock AND above target AND not flagged. AND inside IF returns a clean label when all conditions hold.

Quick formula: flag rows where all three conditions are TRUE:
=IF(AND(B2>100, C2="In stock", D2<>"Hold"), "Ship", "")
AND is TRUE only when every condition passes; IF turns that into a label.

Functions used (tap for the full reference guide):

ANDIF

The example

Only rows passing all checks are flagged.

AB
1RowFlag
2meets allShip
3fails one—

The formula

All-true gate:

=IF(AND(B2>100, C2="In stock", D2<>"Hold"), "Ship", "") // every condition must pass

How it works

AND gates the label:

  1. List each condition inside AND(…) — it’s TRUE only if every one is TRUE.
  2. Wrap in IF to return a readable result instead of TRUE/FALSE.
  3. Use OR instead for “any condition,” or combine: AND(x, OR(y, z)).
  4. For counting how many rows pass, COUNTIFS with the same conditions is the direct route.

AND collapses arrays. AND(range>100) returns a single TRUE/FALSE for the whole range, not per row. For per-row logic across columns it’s perfect; for array conditions, multiply (*) inside SUMPRODUCT instead.

Try it: interactive demo

Live demo

Toggle three checks.

Flag:

Variations

Any condition (OR)

At least one:

=IF(OR(cond1, cond2), "Flag", "")

Count passing rows

Direct:

=COUNTIFS(qty,">100", status,"In stock")

Mixed AND/OR

Group:

=IF(AND(A, OR(B, C)), "Yes", "No")

Pitfalls & errors

AND returns one value. It can’t do per-row logic over a whole column — use SUMPRODUCT with * for that.

Text comparisons. C2="In stock" is case-insensitive but space-sensitive — watch trailing spaces.

Empty result. Returning "" leaves a blank that still counts as non-empty to some functions.

Practice workbook

📊
Download the free Flag Rows Meeting All Conditions (AND) practice workbook
An all-conditions flag with the OR, COUNTIFS, and mixed variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I flag rows that meet all conditions in Excel?
Use AND inside IF: =IF(AND(cond1, cond2, cond3), "Flag", ""). AND is TRUE only when every condition passes.
How do I flag rows meeting any condition?
Use OR: =IF(OR(cond1, cond2), "Flag", "").
How do I count rows meeting all conditions?
Use COUNTIFS with the same criteria, e.g. =COUNTIFS(qty,">100", status,"In stock").

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: IF with AND/OR · COUNTIFS multiple criteria · Highlight entire row

Function references: AND · IF