COUNTIFS Function in Excel – Count With Multiple Conditions

Sometimes in Excel, one condition is not enough.

Real data is usually more detailed and needs more specific filtering.

Imagine you’re looking at a list of customer orders.

You don’t just want to know:

 How many orders are Delivered?

You may want:

• Delivered AND Amount > 1000
• Pending AND Region = North
• Laptop orders AND Qty > 2
• South region AND Delivered

At this point, COUNTIF is not enough because it allows only one condition.

You need a function that checks multiple conditions at the same time.

Scrolling and counting manually?

Nope. Not doing that.

This is exactly where COUNTIFS becomes your best friend.

In simple words:

COUNTIFS = counts cells that meet TWO or MORE conditions at once.

It’s perfect when you want a precise, filtered count based on multiple rules.

What Is the COUNTIFS Function?

The COUNTIFS function counts how many rows match multiple conditions.

It works with:

• numbers
• text
• dates
• greater than / less than
• equal / not equal
• partial matches (wildcards like `*`)

COUNTIFS is very helpful for filtered counting, especially when your data is big.

Syntax:

=COUNTIFS(range1, criteria1, [range2, criteria2], …)

Arguments:

• range1: First range to check
• criteria1: First condition
• range2, criteria2: (Optional) More ranges + conditions

Excel counts a row only if ALL conditions are TRUE.

COUNTIFS Examples:

Here’s an example table to understand COUNTIFS clearly:

Order ID	Customer	Product	Qty	Status	Amount	Region
101	John	Laptop	2	Delivered	$1,200	North
102	Sara	Mouse	1	Pending	$20	South
103	Rakesh	Keyboard	3	Delivered	$90	East
104	Meera	Laptop	1	Cancelled	$600	West
105	David	Monitor	2	Delivered	$300	North
106	Ali	Mouse	5	Pending	$100	East
107	Anita	Laptop	1	Delivered	$600	South
108	Chris	Keyboard	4	Delivered	$120	North
109	Priya	Laptop	3	Pending	$1,800	West
110	Mohan	Mouse	2	Delivered	$40	East

1. Count Delivered Orders From North Region:

=COUNTIFS(E2:E11, “Delivered”, G2:G11, “North”)

Delivered orders in North = 3

2. Count Orders Where Amount > 500 AND Status is Delivered:

=COUNTIFS(F2:F11, “>500”, E2:E11, “Delivered”)

Result → 2

3. Count Laptop Orders With Qty > 1:

=COUNTIFS(C2:C11, “Laptop”, D2:D11, “>1”)

Result → 2 (Orders 101 and 109)

4. Count Orders With Products Starting With “M” (Mouse, Monitor) AND Amount > 20

=COUNTIFS(C2:C11, “M*”, F2:F11, “>20”)

Result → 3

Here “*” is a wildcard for items starting with M.

Why `”M*”` works:

• Matches Mouse
• Matches Monitor
• Matches anything starting with M

When Should You Use COUNTIFS?

Use COUNTIFS when you need to count data based on more than one rule.

Perfect for:

• sales reports
• customer order analysis
• inventory and product tracking
• attendance (e.g., Present AND On Time)
• marks (e.g., >40 AND <90)
• HR reports (e.g., Department = Sales AND Status = Active)
• Logistics reports

COUNTIFS saves a lot of time by filtering and counting automatically.

Related Functions (Quick Guide):

Function	Use Case
COUNT	Count numbers only
COUNTA	Count non-empty cells
COUNTBLANK	Count empty cells
COUNTIF	Count with one condition
COUNTIFS	Count with multiple conditions

In Simple Words:

• COUNTIF → count using ONE condition
• COUNTIFS → count using MULTIPLE conditions
• Works with text, numbers, dates and comparisons.
• Perfect for filtering real data

COUNTIFS gives you accurate results without manual counting.