AVERAGEIF and AVERAGEIFS in Excel - Dashboard Guide

Dashboard Mode - AVERAGEIF and AVERAGEIFS

Dashboard Goal

Find the average sales amounts based on specific conditions like region or product category to understand which areas or products perform best.

Sample Data

Order ID	Region	Product Category	Sales Amount
1001	East	Electronics	250
1002	West	Furniture	450
1003	East	Furniture	300
1004	North	Electronics	200
1005	South	Electronics	150
1006	West	Electronics	400
1007	South	Furniture	350

Dashboard Components

• KPI Card 1: Average Sales in East Region
  Formula: =AVERAGEIF(B2:B8, "East", D2:D8)
  Result: 275
• KPI Card 2: Average Sales for Electronics Category
  Formula: =AVERAGEIF(C2:C8, "Electronics", D2:D8)
  Result: 250
• KPI Card 3: Average Sales in West Region for Furniture
  Formula: =AVERAGEIFS(D2:D8, B2:B8, "West", C2:C8, "Furniture")
  Result: 450
• Table: Average Sales by Region and Product Category
  Formulas:
  - East Electronics: =AVERAGEIFS(D2:D8, B2:B8, "East", C2:C8, "Electronics") -> 250
  - East Furniture: =AVERAGEIFS(D2:D8, B2:B8, "East", C2:C8, "Furniture") -> 300
  - West Electronics: =AVERAGEIFS(D2:D8, B2:B8, "West", C2:C8, "Electronics") -> 400
  - West Furniture: =AVERAGEIFS(D2:D8, B2:B8, "West", C2:C8, "Furniture") -> 450
  - South Electronics: =AVERAGEIFS(D2:D8, B2:B8, "South", C2:C8, "Electronics") -> 150
  - South Furniture: =AVERAGEIFS(D2:D8, B2:B8, "South", C2:C8, "Furniture") -> 350
  - North Electronics: =AVERAGEIFS(D2:D8, B2:B8, "North", C2:C8, "Electronics") -> 200
  - North Furniture: =AVERAGEIFS(D2:D8, B2:B8, "North", C2:C8, "Furniture") -> #DIV/0! (no data)

Dashboard Layout

+---------------------------+---------------------------+
| Average Sales in East     | Average Sales Electronics |
| Region: 275               | Category: 250             |
+---------------------------+---------------------------+
| Average Sales West Region for Furniture: 450             |
+---------------------------------------------------------+
| Average Sales by Region and Product Category Table       |
| +---------+------------+------------+                    |
| | Region  | Electronics| Furniture  |                    |
| +---------+------------+------------+                    |
| | East    | 250        | 300        |                    |
| | West    | 400        | 450        |                    |
| | South   | 150        | 350        |                    |
| | North   | 200        | #DIV/0!    |                    |
| +---------+------------+------------+                    |
+---------------------------------------------------------+

Interactivity

Add a filter dropdown for Region and another for Product Category. When you select a region or category, all KPI cards and the table update to show averages only for the selected filters.

For example, selecting East region updates KPI cards and table to show averages only for East. Selecting Furniture category filters to furniture sales only.

Self Check

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

• KPI Card 1 (Average Sales in East Region): No, because filter is South.
• KPI Card 2 (Average Sales for Electronics): Updates to average sales for Electronics in South = 150.
• KPI Card 3 (Average Sales in West Region for Furniture): No, because filter is South.
• Table: Updates to show only South region averages:
  - Electronics: 150
  - Furniture: 350

Key Result

Dashboard shows average sales by region and product category using AVERAGEIF and AVERAGEIFS formulas.