Excel Tutorial Note: COUNTIFS Function

Objective

A comprehensive tutorial on how to use Excel's COUNTIFS function, including tips, common pitfalls, and advanced scenarios.

---

What is COUNTIFS?

• COUNTIFS counts the number of cells that meet one or more criteria.
• More versatile than COUNTIF, which allows only a single condition.
• Useful for dynamic business requirements where multiple conditions might be added later.

---

Basic Syntax

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

---

Typical Use Cases

1. Single Condition Example

• Task: Count how many times "Shirt white" appears in the Product column.
  • Use cell reference, e.g.:

    =COUNTIFS(ProductRange, "Shirt white")
    

  • Using a cell reference is preferable for flexibility.

2. Multiple Conditions (AND Logic)

• Task: Count "Shirt white" in February.
  • Can be done in any order, just ensure correct range for each criterion.
  • Example:

    =COUNTIFS(ProductRange, "Shirt white", MonthRange, "February")
    

  • All selected ranges must have the same height (number of rows).

3. Three or More Conditions

• Task: Add Country = "USA".
  • Example:

    =COUNTIFS(ProductRange, "Shirt white", MonthRange, "February", CountryRange, "USA")
    

---

OR Conditions with COUNTIFS

• Problem: COUNTIFS by default is AND logic. To count "Shirt white" OR "Shirt blue":
  • Use separate COUNTIFS and add results:

    =COUNTIFS(ProductRange, "Shirt white") + COUNTIFS(ProductRange, "Shirt blue")
    

  • Shortcut: Use SUM with an array for multiple criteria (and use Ctrl+Shift+Enter in Excel 2016 or earlier):

    =SUM(COUNTIFS(ProductRange, {"Shirt white","Shirt blue"}))
    

---

Not Equal To (≠)

• Task: Count products not equal to "Shirt yellow".
  • Use "<>" for not equal (in quotes):

    =COUNTIFS(ProductRange, "<>Shirt yellow")
    

  • For cell references:

    =COUNTIFS(ProductRange, "<>" & A1)
    

    where A1 contains "Shirt yellow".

---

Greater Than / Greater Than or Equal To

• Task: Sales greater than 550.
  • Combine operator with ampersand and reference:

    =COUNTIFS(SalesRange, ">" & 550)
    

  • Greater than or equal:

    =COUNTIFS(SalesRange, ">=" & 550)
    

---

Count Blank / Non-blank Cells

• Blank: Use empty quotation marks:

  =COUNTIFS(CountryRange, "")
  

• Non-blank: Use "<>" with quotation marks:

  =COUNTIFS(CountryRange, "<>")
  

---

Counting Between Two Dates

• Task: Count orders between two dates.
  • Use greater than start date and less than end date (use ampersand with cell references):

    =COUNTIFS(DateRange, ">" & StartDateCell, DateRange, "<" & EndDateCell)
    

  • To include the boundary dates, use ">=" and "<=".

---

Partial Text Matches (Wildcards)

• Begins with: Use "*" wildcard:

  =COUNTIFS(ProductRange, "Shirt*")
  

• Contains: Use "*" at both ends:

  =COUNTIFS(ProductRange, "*" & "blue" & "*")
  

---

Multiple Criteria: Partial Text + Other

• Task: Products contain "blue" AND month is "February":

  =COUNTIFS(ProductRange, "*" & "blue" & "*", MonthRange, "February")
  

---

Tips & Best Practices

• Prefer COUNTIFS over COUNTIF for flexibility.
• All criteria range arguments must have the same length.
• Use cell references for criteria when possible for easier updates.
• Use SUM and array constants for OR logic.
• Wildcards (*) help with partial text matches.
• Use ampersand (&) to concatenate operators and cell references in criterions for dynamic formulas.

---

Common Errors to Avoid

• Mismatched criteria range sizes.
• Forgetting brackets, quotes, or ampersands in operator concatenation.
• Using COUNTIFS for OR logic without SUM/array workaround.

---

Final Note

• COUNTIFS is a robust tool for data analysis in Excel.
• Use it for single/multiple conditions, partial matches, and combining AND/OR logic when needed.
• Experiment with different criteria for dynamic and powerful data calculations.

---

Further Resources:
Download the example file from the video description for follow-along practice.

---

If you found this helpful, leave feedback and suggestions for more content!