Bird
Raised Fist0
Excelspreadsheet~8 mins

COUNTIF and COUNTIFS in Excel - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Dashboard Mode - COUNTIF and COUNTIFS
Dashboard Goal

Find out how many sales meet certain conditions, like counting sales above a target or counting sales by region and product.

Sample Data
Order IDRegionProductSales
1001EastApples120
1002WestOranges80
1003EastBananas150
1004SouthApples90
1005WestBananas200
1006EastOranges110
1007SouthBananas130
Dashboard Components
  • KPI 1: Count of sales above 100
    Formula: =COUNTIF(D2:D8, ">100")
    Result: 5
  • KPI 2: Count of sales in East region
    Formula: =COUNTIF(B2:B8, "East")
    Result: 3
  • KPI 3: Count of sales in West region for Bananas
    Formula: =COUNTIFS(B2:B8, "West", C2:C8, "Bananas")
    Result: 1
  • Table: Sales count by Region and Product
    Formula for East Apples: =COUNTIFS(B2:B8, "East", C2:C8, "Apples") (Result: 1)
    Formula for West Oranges: =COUNTIFS(B2:B8, "West", C2:C8, "Oranges") (Result: 1)
    Formula for South Bananas: =COUNTIFS(B2:B8, "South", C2:C8, "Bananas") (Result: 1)
Dashboard Layout
+----------------------+----------------------+
|  Count Sales > 100   |  Count Sales East    |
|       (KPI 1)        |       (KPI 2)        |
+----------------------+----------------------+
|  Count West Bananas  | Sales Count by Region|
|       (KPI 3)        |    and Product Table  |
+----------------------+----------------------+
Interactivity

Add a filter for Region. When you select a region like "East", all KPIs and the sales count table update to show counts only for that region.

Add a filter for Product. Selecting a product like "Bananas" updates KPIs and the table to count only sales of that product.

Self Check

If you add a filter for Region = South, which components update and what are their new values?

  • KPI 1 (Count of sales above 100): Counts sales above 100 in South region -> 1 (130)
  • KPI 2 (Count of sales East): Updates to 0 because region is South
  • KPI 3 (Count of sales West Bananas): Updates to 0 because region is South
  • Sales count table: Shows counts only for South region products
Key Result
Dashboard counts sales based on conditions using COUNTIF and COUNTIFS formulas.

Practice

(1/5)
1. What does the Excel function COUNTIF(range, criteria) do?
easy
A. Counts the number of cells in a range that meet a single condition
B. Adds all numbers in a range that meet a condition
C. Counts cells with numbers only, ignoring text
D. Finds the maximum value in a range

Solution

  1. Step 1: Understand the function purpose

    The COUNTIF function is designed to count cells that match one specific condition.

  2. Step 2: Compare with other options

    B describes adding numbers meeting a condition (like SUMIF), C describes counting numbers only (like COUNT), and D describes finding the maximum (like MAX), none of which is COUNTIF.

  3. Final Answer:

    Counts the number of cells in a range that meet a single condition -> Option A

  4. Quick Check:

    COUNTIF counts matching cells [OK]
Hint: COUNTIF counts cells matching one condition only [OK]
Common Mistakes:
  • Confusing COUNTIF with SUMIF
  • Thinking COUNTIF counts multiple conditions
  • Assuming COUNTIF only counts numbers
2. Which of the following is the correct syntax for counting cells in range A1:A10 that equal 5 using COUNTIF?
easy
A. =COUNTIF(A1:A10, 5)
B. =COUNTIF(5, A1:A10)
C. =COUNTIF(A1:A10, ">=5")
D. =COUNTIF(A1:A10; 5)

Solution

  1. Step 1: Recall COUNTIF syntax

    The correct syntax is COUNTIF(range, criteria). The criteria can be a number without quotes.

  2. Step 2: Evaluate each option

    A uses ">=5" which counts cells greater than or equal to 5, not exactly 5; B reverses arguments, which is wrong; C uses 5 directly, which is correct and simpler; D uses semicolon which is locale-dependent and may cause error in some Excel versions.

  3. Final Answer:

    =COUNTIF(A1:A10, 5) -> Option A

  4. Quick Check:

    COUNTIF(range, criteria) with number criteria [OK]
Hint: Use =COUNTIF(range, number) without quotes for numbers [OK]
Common Mistakes:
  • Swapping range and criteria arguments
  • Using semicolon instead of comma in some locales
  • Using '>=5' instead of '5' for exact match
3. Given the data in cells A1:A5: {3, 5, 5, 7, 5}, what is the result of =COUNTIFS(A1:A5, ">=5", A1:A5, "<7")?
medium
A. 1
B. 2
C. 3
D. 4

Solution

  1. Step 1: Understand the COUNTIFS conditions

    COUNTIFS counts cells where values are >=5 AND <7.

  2. Step 2: Check each value in A1:A5

    Values: 3 (no), 5 (yes), 5 (yes), 7 (no, equals 7 but <7 fails), 5 (yes). So three 5s qualify, but 7 does not.

  3. Step 3: Confirm the count

    Three values are 5, but condition is >=5 and <7, so 5 counts. So count is 3.

  4. Final Answer:

    3 -> Option C

  5. Quick Check:

    Values between 5 and less than 7 = 3 [OK]
Hint: COUNTIFS counts cells meeting all conditions simultaneously [OK]
Common Mistakes:
  • Counting values equal to 7 incorrectly
  • Confusing COUNTIF with COUNTIFS
  • Misreading the logical AND in COUNTIFS
4. You want to count cells in B1:B10 that are greater than 10 and less than 20. Which formula has an error?
medium
A. =COUNTIFS(B1:B10, ">10", B1:B10, "<20")
B. =COUNTIF(B1:B10, ">10") + COUNTIF(B1:B10, "<20")
C. =COUNTIFS(B1:B10, "<20", B1:B10, ">10")
D. =COUNTIFS(B1:B10, ">10",B1:B10,"<20")

Solution

  1. Step 1: Understand the goal

    You want to count cells greater than 10 AND less than 20, meaning both conditions must be true simultaneously.

  2. Step 2: Analyze each formula

    A adds two COUNTIF results, which counts cells greater than 10 plus cells less than 20 separately, causing double counting. The other options use COUNTIFS correctly with both conditions combined.

  3. Final Answer:

    =COUNTIF(B1:B10, ">10") + COUNTIF(B1:B10, "<20") -> Option B

  4. Quick Check:

    Adding counts separately causes double counting [OK]
Hint: Use COUNTIFS for multiple conditions, not sum of COUNTIFs [OK]
Common Mistakes:
  • Adding COUNTIF results instead of using COUNTIFS
  • Misunderstanding AND vs OR logic
  • Using wrong operators in criteria
5. You have a sales table with columns: Region (A2:A10), Sales (B2:B10). Which formula counts sales greater than 1000 in the "East" region?
hard
A. =COUNTIF(A2:A10, "East") + COUNTIF(B2:B10, ">1000")
B. =COUNTIFS(A2:A10, ">1000", B2:B10, "East")
C. =COUNTIF(A2:A10, "East" & B2:B10, ">1000")
D. =COUNTIFS(A2:A10, "East", B2:B10, ">1000")

Solution

  1. Step 1: Identify conditions and ranges

    You want to count rows where Region is "East" AND Sales > 1000. Region is in A2:A10, Sales in B2:B10.

  2. Step 2: Match conditions to correct ranges

    =COUNTIFS(A2:A10, "East", B2:B10, ">1000") correctly uses COUNTIFS with Region range and criteria "East", and Sales range with ">1000". =COUNTIF(A2:A10, "East") + COUNTIF(B2:B10, ">1000") adds separate COUNTIFs, which is incorrect. =COUNTIF(A2:A10, "East" & B2:B10, ">1000") concatenates ranges incorrectly. =COUNTIFS(A2:A10, ">1000", B2:B10, "East") swaps criteria and ranges incorrectly.

  3. Final Answer:

    =COUNTIFS(A2:A10, "East", B2:B10, ">1000") -> Option D

  4. Quick Check:

    COUNTIFS matches multiple conditions on correct ranges [OK]
Hint: Use COUNTIFS with each condition and its range paired [OK]
Common Mistakes:
  • Adding separate COUNTIF results instead of COUNTIFS
  • Mixing up criteria and ranges
  • Concatenating ranges incorrectly
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard