COUNT and COUNTA functions in Excel - Dashboard Guide

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 ID	Product	Quantity	Price	Notes
101	Pen	10	1.5	First order
102	Notebook	5	3.0
103	Pen	15	1.5	Urgent
104
105	Marker	7	2.0
	Notebook	3	3.0	Repeat
107	Pen	8	1.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.