SUMIF and SUMIFS in Excel - Dashboard Guide

Dashboard Mode - SUMIF and SUMIFS

Goal

Find total sales based on one or more conditions like region or product.

Sample Data

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

Dashboard Components

• Total Sales in East Region
  Formula: =SUMIF(B2:B8, "East", D2:D8)
  Result: 450 (120 + 200 + 130)
• Total Sales of Apples
  Formula: =SUMIF(C2:C8, "Apples", D2:D8)
  Result: 370 (120 + 90 + 160)
• Total Sales in West Region for Oranges
  Formula: =SUMIFS(D2:D8, B2:B8, "West", C2:C8, "Oranges")
  Result: 150
• Total Sales of Bananas in North Region
  Formula: =SUMIFS(D2:D8, B2:B8, "North", C2:C8, "Bananas")
  Result: 80

Dashboard Layout

+-----------------------------+
| Total Sales in East Region  |
|           450               |
+-----------------------------+
+-----------------------------+
| Total Sales of Apples       |
|           370               |
+-----------------------------+
+---------------------------------------+
| Total Sales in West Region for Oranges|
|                 150                   |
+---------------------------------------+
+---------------------------------------+
| Total Sales of Bananas in North Region|
|                  80                   |
+---------------------------------------+

Interactivity

Add a filter dropdown for Region and Product. When you select a region or product, the totals update to show sales only for that selection.

For example, selecting East in Region filter updates all components to show sales only for East region. Selecting Apples in Product filter updates totals to Apples only.

Self Check

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

• Total Sales in East Region: Updates to 0 (no East sales in West filter)
• Total Sales of Apples: Updates to 160 (only Apples in West region)
• Total Sales in West Region for Oranges: Remains 150
• Total Sales of Bananas in North Region: Updates to 0 (no North region in West filter)

Key Result

Dashboard shows total sales filtered by region and product using SUMIF and SUMIFS formulas.