Bird
Raised Fist0
Excelspreadsheet~8 mins

COUNT and COUNTA functions in Excel - Dashboard Guide

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash

Start learning this pattern below

Jump into concepts and practice - no test required

or
Recommended
Test this pattern10 questions across easy, medium, and hard to know if this pattern is strong
Dashboard Mode - COUNT and COUNTA functions
Dashboard Goal

Understand how many sales entries are recorded and how many cells contain any data in the sales report.

Sample Data
Sale IDProductQuantityPriceNotes
101Pen101.5First order
102Notebook53.0
103Pen151.5Urgent
104
105Marker72.0
Notebook33.0Repeat
107Pen81.5
Dashboard Components
  • KPI Card: Count of Sale IDs
    Formula: =COUNT(A2:A8)
    Explanation: Counts how many cells in Sale ID column have numbers.
    Result: 6 (because one Sale ID is missing)
  • KPI Card: Count of Non-Empty Cells in Product Column
    Formula: =COUNTA(B2:B8)
    Explanation: Counts all cells with any data (text or number) in Product column.
    Result: 6 (one cell is empty)
  • KPI Card: Count of Non-Empty Cells in Notes Column
    Formula: =COUNTA(E2:E8)
    Explanation: Counts all cells with any text in Notes column.
    Result: 3 (three notes are filled)
Dashboard Layout
+----------------------+------------------------------+------------------------------+
| Count of Sale IDs     | Count of Products             | Count of Notes                |
| (COUNT)              | (COUNTA)                     | (COUNTA)                     |
|       [ 6 ]           |          [ 6 ]                |          [ 3 ]                |
+----------------------+------------------------------+------------------------------+
|                              Sales Data Table                               |
|                             (Rows 2 to 8)                                |
+---------------------------------------------------------------------------+
Interactivity

Add a filter for Product to select a specific product like "Pen". When you choose "Pen":

  • The COUNT(A2:A8) updates to count Sale IDs only for "Pen" rows.
  • The COUNTA(B2:B8) updates to count only visible Product cells after filter.
  • The COUNTA(E2:E8) updates to count Notes only for filtered rows.
  • The Sales Data Table shows only rows with Product = "Pen".
Self Check

If you add a filter for Product = "Notebook", which components update and what are their new values?

  • Count of Sale IDs (COUNT): Counts Sale IDs with "Notebook" (2 rows: 102 and blank Sale ID row). Result: 1 (only one numeric Sale ID)
  • Count of Products (COUNTA): Counts Product cells with "Notebook". Result: 2
  • Count of Notes (COUNTA): Counts Notes cells for "Notebook" rows. Result: 1 (only one note "Repeat")
  • Sales Data Table: Shows only rows with Product "Notebook".
Key Result
Dashboard shows counts of numeric Sale IDs and non-empty cells in Product and Notes columns using COUNT and COUNTA.

Practice

(1/5)
1. What does the COUNT function do in Excel?
easy
A. Counts only cells with numbers
B. Counts all non-empty cells
C. Counts only empty cells
D. Counts cells with text only

Solution

  1. Step 1: Understand the purpose of COUNT

    The COUNT function counts only cells that contain numbers, ignoring text or empty cells.

  2. Step 2: Compare with other options

    COUNTA counts all non-empty cells, so it is not the same as COUNT.

  3. Final Answer:

    Counts only cells with numbers -> Option A

  4. Quick Check:

    COUNT counts numbers only [OK]
Hint: COUNT counts numbers only, not text or blanks [OK]
Common Mistakes:
  • Thinking COUNT counts all non-empty cells
  • Confusing COUNT with COUNTA
  • Assuming COUNT counts text cells
2. Which of these formulas correctly counts all non-empty cells in range A1:A5?
easy
A. =COUNTA(A1:A5)
B. =COUNT(A1:A5)
C. =COUNTBLANK(A1:A5)
D. =COUNTIF(A1:A5, "*")

Solution

  1. Step 1: Identify the function that counts all non-empty cells

    COUNTA counts all cells that are not empty, including numbers, text, and errors.

  2. Step 2: Check other options

    COUNT counts only numbers, COUNTBLANK counts empty cells, COUNTIF with "*" counts cells with text but not numbers.

  3. Final Answer:

    =COUNTA(A1:A5) -> Option A

  4. Quick Check:

    COUNTA counts all non-empty cells [OK]
Hint: Use COUNTA to count all filled cells, not just numbers [OK]
Common Mistakes:
  • Using COUNT instead of COUNTA for all data
  • Confusing COUNTBLANK with COUNTA
  • Using COUNTIF("*") which misses numbers
3. Given the cells A1=10, A2="Hello", A3=, A4=5, A5="", what is the result of =COUNT(A1:A5)?
medium
A. 3
B. 1
C. 2
D. 4

Solution

  1. Step 1: Identify which cells contain numbers

    Cells A1=10 and A4=5 are numbers. A2 is text, A3 is empty, A5 is empty string (counts as empty).

  2. Step 2: COUNT counts only numbers

    So COUNT(A1:A5) counts 2 cells with numbers.

  3. Final Answer:

    2 -> Option C

  4. Quick Check:

    COUNT counts only numbers = 2 [OK]
Hint: COUNT counts only numeric cells, ignore text and blanks [OK]
Common Mistakes:
  • Counting text cells as numbers
  • Counting empty strings as non-empty
  • Confusing COUNT with COUNTA
4. You want to count all non-empty cells in B1:B6, but your formula =COUNT(B1:B6) returns 3. What is the likely problem?
medium
A. The formula syntax is incorrect
B. B1:B6 contains text and blanks, COUNT counts only numbers
C. COUNT counts empty cells by mistake
D. You need to use COUNTBLANK instead

Solution

  1. Step 1: Understand what COUNT does

    COUNT counts only numeric cells, ignoring text and blanks.

  2. Step 2: Identify why result is 3

    If B1:B6 has text or blanks, COUNT returns only the number cells, so 3 means 3 numeric cells.

  3. Final Answer:

    B1:B6 contains text and blanks, COUNT counts only numbers -> Option B

  4. Quick Check:

    COUNT counts numbers only, so text cells are ignored [OK]
Hint: Use COUNTA to count all filled cells, not COUNT [OK]
Common Mistakes:
  • Assuming COUNT counts text cells
  • Using COUNTBLANK to count filled cells
  • Thinking formula syntax is wrong
5. You have a list in C1:C8 with numbers, text, and some empty cells. Which formula counts all cells that are not empty, including numbers and text, but excludes empty cells?
hard
A. =COUNTBLANK(C1:C8)
B. =COUNT(C1:C8)
C. =COUNTIF(C1:C8, "<>")
D. =COUNTA(C1:C8)

Solution

  1. Step 1: Identify the function that counts all non-empty cells

    COUNTA counts all cells that are not empty, including numbers and text.

  2. Step 2: Check other options

    COUNT counts only numbers, COUNTIF with "<>" counts non-empty but may miss some cases, COUNTBLANK counts empty cells.

  3. Final Answer:

    =COUNTA(C1:C8) -> Option D

  4. Quick Check:

    COUNTA counts all non-empty cells [OK]
Hint: Use COUNTA to count all filled cells, numbers or text [OK]
Common Mistakes:
  • Using COUNT which misses text cells
  • Using COUNTBLANK which counts empty cells
  • Using COUNTIF("<>") which can be tricky