COUNTIF and COUNTIFS in Google Sheets - Dashboard Guide

Dashboard Mode - COUNTIF and COUNTIFS

Dashboard Goal

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

Sample Data

Order ID	Product	Region	Sales
1001	Apples	East	150
1002	Bananas	West	200
1003	Apples	West	120
1004	Oranges	East	180
1005	Bananas	East	160
1006	Apples	East	130
1007	Oranges	West	170

Dashboard Components

• KPI Card 1: Count of all sales of Apples
  =COUNTIF(B2:B8, "Apples")
  Result: 3
• KPI Card 2: Count of sales in East region
  =COUNTIF(C2:C8, "East")
  Result: 4
• KPI Card 3: Count of sales of Bananas in East region
  =COUNTIFS(B2:B8, "Bananas", C2:C8, "East")
  Result: 1
• Table: Count of sales by product and region
  =COUNTIFS(B2:B8, E2, C2:C8, F1) (used in a grid with products in E column and regions in F row)

Dashboard Layout

+----------------+----------------+----------------+
|  KPI: Apples   |  KPI: East     |  KPI: Bananas  |
|  Count = 3     |  Count = 4     |  in East = 1   |
+----------------+----------------+----------------+
|                Sales Count by Product and Region    |
|  +---------+---------+---------+                    |
|  |         |  East   |  West   |                    |
|  +---------+---------+---------+                    |
|  | Apples  |    2    |    1    |                    |
|  | Bananas |    1    |    1    |                    |
|  | Oranges |    1    |    1    |                    |
|  +---------+---------+---------+                    |
+-----------------------------------------------------+

Interactivity

Add a filter for Region. When you select a region like "East", all KPI cards and the table update to show counts only for that region. For example, the "Count of Apples" KPI will show how many Apple sales are in East only. The table will show counts for each product but only in the selected region.

Self Check

If you add a filter for Product = Oranges, which components update and what will they show?

• KPI Card 1 (Apples count) updates to 0 because no Apples are selected.
• KPI Card 2 (East region count) updates to count of Oranges in East (1).
• KPI Card 3 (Bananas in East) updates to 0 because product is filtered to Oranges.
• The table updates to show counts only for Oranges by region.

Key Result

Dashboard counts sales by product and region using COUNTIF and COUNTIFS formulas.