COUNTIF and COUNTIFS in Excel - Dashboard Guide

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 ID	Region	Product	Sales
1001	East	Apples	120
1002	West	Oranges	80
1003	East	Bananas	150
1004	South	Apples	90
1005	West	Bananas	200
1006	East	Oranges	110
1007	South	Bananas	130

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.