Excel beginner countifs count multiple criteria dashboard

COUNTIFS With Multiple Conditions in Excel

COUNTIFS With Multiple Conditions is a Excel function that countifs counts rows where all criteria match. Formula Genius generates and validates this formula automatically from a plain-English prompt.

Count rows that match two or more criteria simultaneously. Essential for dashboards, KPI tracking, and data validation.

Generate This Formula Browse Library

The Formula

Prompt

"Count orders where status is Completed and amount is greater than 500"

Excel 
=COUNTIFS(A2:A100,"Completed",B2:B100,">500")

COUNTIFS counts rows where ALL criteria match. Here it counts rows where column A is Completed AND column B exceeds 500.

Step-by-Step Breakdown

  1. A2:A100,"Completed" — first criteria: status must be Completed
  2. B2:B100,">500" — second criteria: amount must exceed 500
  3. Both conditions must be TRUE in the same row to be counted
  4. You can add more criteria pairs for additional conditions

Edge Cases & Warnings

  • Returns 0 when no rows match (no error to handle)
  • Text matching is case-insensitive
  • Wildcards: use * for any characters, ? for single character
  • Number criteria must be in quotes when using operators: ">500" not >500

Examples

Prompt

"Status="Completed", Amount>500"

Excel 
Count of high-value completed orders
Prompt

"Status="Pending", Amount>1000"

Excel 
Count of large pending orders

Frequently Asked Questions

Can COUNTIFS use OR logic?

Not directly. Add multiple COUNTIFS: =COUNTIFS(A:A,"Done")+COUNTIFS(A:A,"Shipped") counts rows that are Done OR Shipped.

How many criteria can COUNTIFS handle?

Up to 127 criteria pairs. In practice, if you need more than 5-6, consider restructuring your data or using a PivotTable.

Can't find what you need?

Describe any formula in plain English and Formula Genius will generate, explain, and validate it — instantly.

Generate Custom Formula See Examples