COUNTIF vs COUNTIFS in Excel

Use COUNTIF when you need to count cells that match one condition. Use COUNTIFS when you need to count rows that match two or more conditions.

Quick examples:

=COUNTIF(D2:D8,"Paid")

=COUNTIFS(B2:B8,"East",D2:D8,"Paid")

The first formula counts paid orders. The second counts orders where Region is East and Status is Paid.

If you are learning the whole COUNT function family, start with the Excel COUNT function tutorial. Use this guide when your specific question is which conditional counting formula to choose.

1. COUNTIF vs COUNTIFS: Quick Difference

Both functions count matching cells, but they have different formula patterns.

The practical rule is simple:

In new workbooks, COUNTIFS is often a good default because it can handle one condition now and more conditions later. COUNTIF is still shorter and easier to read for a truly single-condition check.

2. Example Data

Suppose you have this order list:

We will use this table for the examples below.

3. Use COUNTIF for One Condition

COUNTIF has two arguments:

=COUNTIF(range, criteria)

To count paid orders:

=COUNTIF(D2:D8,"Paid")

Result: 5

Excel checks each cell in D2:D8 and counts the cells that equal Paid.

Other common COUNTIF examples:

Use COUNTIF when the question can be answered by checking one range against one rule.

4. Use COUNTIFS for Multiple Conditions

COUNTIFS uses pairs of criteria ranges and criteria:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)

To count paid orders in the East region:

=COUNTIFS(B2:B8,"East",D2:D8,"Paid")

Result: 3

Excel only counts rows where both conditions are true:

This is the key difference: COUNTIFS applies AND logic. Every condition must match on the same row.

5. COUNTIFS Can Also Handle One Condition

You can use COUNTIFS with just one criteria pair:

=COUNTIFS(D2:D8,"Paid")

That returns the same result as:

=COUNTIF(D2:D8,"Paid")

So why not always use COUNTIFS?

For simple sheets, shorter is fine. For dashboards and templates, consistency often matters more.

6. Criteria Examples for Text, Numbers, and Dates

Both functions use the same criteria style.

Text Criteria

Exact text needs quotation marks:

=COUNTIF(B2:B8,"East")

Wildcards help with partial text:

=COUNTIF(C2:C8,"*Pro*")

The asterisk means any number of characters before or after Pro.

Number Criteria

Comparison operators also go inside quotation marks:

=COUNTIF(E2:E8,">=500")

If the threshold is in a cell, join the operator and the cell reference:

=COUNTIF(E2:E8,">="&H2)

Date Criteria

Date ranges usually need COUNTIFS because there are two conditions: on or after the start date, and on or before the end date.

Formula:

=COUNTIFS(A2:A8,">="&H2,A2:A8,"<="&H3)

Result: 5

For a date range plus another condition, add another criteria pair:

=COUNTIFS(A2:A8,">="&H2,A2:A8,"<="&H3,B2:B8,"East")

Result: 3

7. COUNTIF vs COUNTIFS for Duplicates

For duplicates in one column, COUNTIF is usually enough:

=IF(COUNTIF($A$2:$A$100,A2)>1,"Duplicate","Unique")

For duplicate rows defined by multiple columns, use COUNTIFS:

=IF(COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)>1,"Duplicate row","Unique")

The first formula asks, "Does this value appear more than once?" The second asks, "Does this combination of values appear more than once?"

For the full review workflow, see How to Find Duplicates in Excel.

8. COUNTIF vs COUNTIFS for Comparing Lists

When comparing two lists, COUNTIF is often the cleanest formula:

=IF(COUNTIF($D$2:$D$100,A2)>0,"Found","Missing")

This checks whether the value in A2 appears anywhere in the second list.

Use COUNTIFS when the match depends on more than one column:

=IF(COUNTIFS($D$2:$D$100,A2,$E$2:$E$100,B2)>0,"Found","Missing")

This checks whether the ID and Region both match. For a broader walkthrough, see How to Compare Two Columns in Excel.

9. Common COUNTIF and COUNTIFS Mistakes

Mistake 1: Using COUNTIF for Two Conditions

This will not work:

=COUNTIF(B2:B8,"East",D2:D8,"Paid")

COUNTIF accepts only one range and one criterion. Use COUNTIFS instead:

=COUNTIFS(B2:B8,"East",D2:D8,"Paid")

Mistake 2: Mismatched COUNTIFS Ranges

Every criteria range in COUNTIFS must be the same size:

=COUNTIFS(B2:B8,"East",D2:D7,"Paid")

This is wrong because one range has seven cells and the other has six. Use:

=COUNTIFS(B2:B8,"East",D2:D8,"Paid")

Mistake 3: Forgetting Quotation Marks Around Operators

Excel treats comparison operators as criteria text:

=COUNTIF(E2:E8,">=500")

When the value is in another cell, use &:

=COUNTIF(E2:E8,">="&H2)

Mistake 4: Expecting COUNTIFS to Mean OR

COUNTIFS uses AND logic. This counts rows that are both East and West, which is impossible in one Region cell:

=COUNTIFS(B2:B8,"East",B2:B8,"West")

For OR logic, add separate formulas:

=COUNTIF(B2:B8,"East")+COUNTIF(B2:B8,"West")

Mistake 5: Hidden Spaces or Inconsistent Labels

COUNTIF and COUNTIFS compare the actual cell values. East and East are different because the second value has a trailing space.

If counts look wrong, clean the data first with Excel Data Cleaning or standardize labels with Find and Replace in Excel.

10. Practice Example

Using the order table from section 2, answer these questions:

Reference answers:

11. Summary

Use COUNTIF for a single condition and COUNTIFS for multiple conditions. Choose COUNTIFS when a formula may grow from one condition to several, when you are building dashboards, or when you need date ranges. Remember that COUNTIFS uses AND logic, all criteria ranges must be the same size, and operators like >= need quotation marks.

12. Related Guides

• Learn the whole COUNT family in the Excel COUNT function tutorial.
• Find repeated values with How to Find Duplicates in Excel.
• Compare lists with How to Compare Two Columns in Excel.
• Add matching rows instead of counting them with Excel SUMIF and SUMIFS.
• Add matching totals instead of counting them with SUMIF vs SUMIFS in Excel.
• Return labels from multi-condition logic with IF with AND OR in Excel.
• Return labels or decisions from conditions with the Excel IF function tutorial.
• Return matching rows with the Excel FILTER function tutorial.