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.

Practice

1. What does the SUMIF function do in Excel?

easy

A. Adds numbers in a range that meet one condition

B. Adds numbers in multiple ranges without conditions

C. Counts how many cells meet a condition

D. Finds the average of numbers in a range

Solution

1. Step 1: Understand the purpose of SUMIF

   SUMIF adds values only if they meet a single specified condition.

2. Step 2: Compare with other options

   Counting cells or averaging are different functions; SUMIF sums conditionally.

3. Final Answer:

   Adds numbers in a range that meet one condition -> Option A

4. Quick Check:

   SUMIF = sum with one condition [OK]

Hint: SUMIF sums with one condition, SUMIFS with many [OK]

Common Mistakes:

• Confusing SUMIF with COUNTIF
• Thinking SUMIF works with multiple conditions
• Using SUMIF to average values

2. Which of these is the correct syntax for the SUMIFS function?

easy

A. SUMIFS(criteria_range1, criteria1, sum_range)

B. SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

C. SUMIFS(sum_range, criteria1, criteria_range1)

D. SUMIFS(sum_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall SUMIFS syntax

   SUMIFS requires sum_range first, then pairs of criteria_range and criteria.

2. Step 2: Check each option

   Only SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) follows the correct order: sum_range, criteria_range1, criteria1, etc.

3. Final Answer:

   SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) -> Option B

4. Quick Check:

   SUMIFS syntax = sum_range + criteria pairs [OK]

Hint: SUMIFS starts with sum_range, then criteria pairs [OK]

Common Mistakes:

• Swapping sum_range and criteria_range
• Missing criteria pairs
• Using SUMIF syntax for SUMIFS

3. Given this data:

A1:A5 = {10, 20, 30, 40, 50}
B1:B5 = {"Yes", "No", "Yes", "No", "Yes"}

What is the result of =SUMIF(B1:B5, "Yes", A1:A5)?

medium

A. 150

B. 100

C. 120

D. 90

Solution

1. Step 1: Identify matching cells in B1:B5

   Cells with "Yes" are B1, B3, B5 corresponding to A1=10, A3=30, A5=50.

2. Step 2: Sum corresponding values in A1:A5

   Sum = 10 + 30 + 50 = 90.

3. Final Answer:

   90 -> Option D

4. Quick Check:

   Sum of A where B = "Yes" is 90 [OK]

Hint: Sum values where criteria matches in criteria_range [OK]

Common Mistakes:

• Summing all values ignoring criteria
• Confusing Yes and No cells
• Using SUM instead of SUMIF

4. You wrote this formula: =SUMIFS(A1:A5, B1:B5, "Yes", C1:C5, ">10") but it returns #VALUE!. What is the likely problem?

medium

A. Sum range and criteria ranges are different sizes

B. SUMIFS cannot handle multiple criteria

C. Criteria ">10" should be in quotes

D. You must use SUMIF, not SUMIFS

Solution

1. Step 1: Check criteria syntax

   Criteria ">10" is correctly in quotes, so no error there.

2. Step 2: Verify range sizes

   SUMIFS requires sum_range and all criteria_ranges to be the same size; mismatch causes #VALUE! error.

3. Final Answer:

   Sum range and criteria ranges are different sizes -> Option A

4. Quick Check:

   Range size mismatch causes SUMIFS to return #VALUE! [OK]

Hint: Ensure all ranges in SUMIFS have same size [OK]

Common Mistakes:

• Forgetting to quote criteria with operators
• Using SUMIF syntax for multiple criteria
• Assuming SUMIFS sums without matching sizes

5. You have sales data:

Product: A2:A7 = {"Pen", "Pen", "Book", "Pen", "Book", "Book"}
Region: B2:B7 = {"East", "West", "East", "East", "West", "East"}
Sales: C2:C7 = {100, 200, 150, 120, 180, 130}

Which formula sums sales of "Pen" in the "East" region?

hard

A. =SUMIFS(A2:A7, C2:C7, "Pen", B2:B7, "East")

B. =SUMIF(A2:A7, "Pen", C2:C7) + SUMIF(B2:B7, "East", C2:C7)

C. =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East")

D. =SUMIF(C2:C7, "Pen", B2:B7)

Solution

1. Step 1: Identify correct function for multiple conditions

   SUMIFS sums with multiple criteria; SUMIF only handles one.

2. Step 2: Check formula arguments

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") correctly uses sum_range C2:C7, criteria ranges A2:A7 and B2:B7 with criteria "Pen" and "East".

3. Final Answer:

   =SUMIFS(C2:C7, A2:A7, "Pen", B2:B7, "East") -> Option C

4. Quick Check:

   SUMIFS with product and region criteria sums sales [OK]

Hint: Use SUMIFS for multiple conditions, not SUMIF [OK]

Common Mistakes:

• Adding two SUMIF results instead of using SUMIFS
• Mixing sum_range and criteria_range order
• Using SUMIF with multiple criteria