SUMIF and SUMIFS in Google Sheets - Dashboard Guide

Dashboard Mode - SUMIF and SUMIFS

Goal

Find total sales based on conditions like product type or region to understand which products or areas perform best.

Sample Data

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

Dashboard Components

• Total Sales for Apples
  Formula: =SUMIF(B2:B8, "Apples", D2:D8)
  Result: 490 (120 + 200 + 170)
• Total Sales in East Region
  Formula: =SUMIF(C2:C8, "East", D2:D8)
  Result: 570 (120 + 100 + 180 + 170)
• Total Sales for Bananas in East Region
  Formula: =SUMIFS(D2:D8, B2:B8, "Bananas", C2:C8, "East")
  Result: 180
• Total Sales for Oranges in West Region
  Formula: =SUMIFS(D2:D8, B2:B8, "Oranges", C2:C8, "West")
  Result: 130

Dashboard Layout

+---------------------------+---------------------------+
| Total Sales for Apples    | Total Sales in East Region|
|           490             |            570            |
+---------------------------+---------------------------+
| Total Sales for Bananas in East Region | Total Sales for Oranges in West Region |
|               180                    |                130                   |
+-------------------------------------+-------------------------------------+

Interactivity

Add a filter dropdown for Product and Region. When you select a product or region, all totals update to show sales only for the chosen filters. For example, selecting "Apples" updates the total sales cards to only count Apples. Selecting "West" updates totals to only count sales in the West region.

Self Check

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

• Total Sales for Apples: 200 (only Apples in West)
• Total Sales in East Region: 0 (East region filtered out)
• Total Sales for Bananas in East Region: 0 (East region filtered out)
• Total Sales for Oranges in West Region: 130 (Oranges in West)

Key Result

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

Practice

1. What does the SUMIF function do in Google Sheets?

easy

A. Adds numbers that meet one specific condition

B. Adds all numbers in a range regardless of conditions

C. Counts how many cells meet a condition

D. Finds the average of numbers that meet multiple conditions

Solution

1. Step 1: Understand the purpose of SUMIF

   The SUMIF function adds values only if they meet one condition you specify.

2. Step 2: Compare with other options

   Options A, B, and C describe different functions like AVERAGEIFS, SUM, and COUNTIF, not SUMIF.

3. Final Answer:

   Adds numbers that meet one specific 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
• Mixing SUMIF with AVERAGE functions

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

easy

A. SUMIFS(sum_range, criteria1, criteria_range1)

B. SUMIFS(criteria_range1, criteria1, sum_range)

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

D. SUMIFS(sum_range, criteria_range1, criteria1)

Solution

1. Step 1: Recall SUMIFS syntax

   The correct syntax is SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

2. Step 2: Check each option

   SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2) matches the correct order: sum_range first, then pairs of criteria ranges and criteria. Others have wrong order or missing parts.

3. Final Answer:

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

4. Quick Check:

   SUMIFS = sum_range first, then criteria pairs [OK]

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

Common Mistakes:

• Putting criteria before sum_range
• Missing criteria_range or criteria pairs
• Using SUMIF syntax for SUMIFS

3. Given this data in columns A and B:

A1:A5 = {"Apple", "Banana", "Apple", "Orange", "Banana"}
B1:B5 = {10, 20, 30, 40, 50}

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

medium

A. 20

B. 70

C. 50

D. 30

Solution

1. Step 1: Identify matching rows for "Banana"

   In A1:A5, "Banana" appears in A2 and A5.

2. Step 2: Sum corresponding values in B1:B5

   Values at B2 and B5 are 20 and 50. Sum = 20 + 50 = 70.

3. Final Answer:

   70 -> Option B

4. Quick Check:

   Sum of Banana values = 70 [OK]

Hint: Sum values where criteria matches in same row [OK]

Common Mistakes:

• Summing only first match
• Adding values from wrong rows
• Confusing criteria with sum range

4. You wrote this formula: =SUMIFS(B1:B5, A1:A5, "Apple", C1:C5, ">10")
But column C is empty. What will happen?

medium

A. The formula returns 0 because no rows meet all conditions

B. The formula returns an error because C1:C5 is empty

C. The formula sums all values in B1:B5 ignoring conditions

D. The formula sums values where A1:A5 is "Apple" only

Solution

1. Step 1: Understand the conditions

   The formula sums B1:B5 where A1:A5 = "Apple" AND C1:C5 > 10.

2. Step 2: Check empty C1:C5

   Since C1:C5 is empty, no cells are > 10, so no rows match both conditions.

3. Final Answer:

   The formula returns 0 because no rows meet all conditions -> Option A

4. Quick Check:

   Empty criteria range means no matches, sum = 0 [OK]

Hint: Empty criteria range means no matches, sum is zero [OK]

Common Mistakes:

• Expecting an error from empty range
• Ignoring second condition
• Thinking it sums all "Apple" rows anyway

5. You have this data:

A1:A6 = {"East", "West", "East", "North", "West", "East"}
B1:B6 = {100, 200, 150, 300, 250, 50}
C1:C6 = {"Yes", "No", "Yes", "No", "Yes", "No"}

Which formula sums values in B where region is "East" and status is "Yes"?

hard

A. =SUMIFS(B1:B6, A1:A6, "East")

B. =SUMIF(A1:A6, "East", B1:B6) + SUMIF(C1:C6, "Yes", B1:B6)

C. =SUMIF(B1:B6, "East", C1:C6)

D. =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes")

Solution

1. Step 1: Understand the requirement

   We want to sum B values where A = "East" AND C = "Yes".

2. Step 2: Check each formula

   =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes") uses SUMIFS with both conditions correctly. =SUMIF(A1:A6, "East", B1:B6) + SUMIF(C1:C6, "Yes", B1:B6) sums separately and adds, which is incorrect. =SUMIFS(B1:B6, A1:A6, "East") misses second condition. =SUMIF(B1:B6, "East", C1:C6) has wrong argument order.

3. Final Answer:

   =SUMIFS(B1:B6, A1:A6, "East", C1:C6, "Yes") -> Option D

4. Quick Check:

   SUMIFS sums with multiple conditions [OK]

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

Common Mistakes:

• Adding separate SUMIF results instead of using SUMIFS
• Missing one condition in formula
• Using wrong argument order