SUMIF and SUMIFS in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUMIF function do in Excel?

SUMIF adds up the numbers in a range that meet one specific condition. For example, it can add all sales amounts where the product is "Apples."

beginner

How is SUMIFS different from SUMIF?

SUMIFS adds numbers based on multiple conditions. For example, it can add sales where the product is "Apples" and the region is "West."

beginner

Write the syntax of the SUMIF function.

SUMIF(range, criteria, [sum_range])
• range: cells to check the condition
• criteria: condition to meet
• sum_range: cells to add if condition is true (optional)

beginner

Write the syntax of the SUMIFS function.

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• sum_range: cells to add
• criteria_range1: first range to check
• criteria1: first condition
• Additional pairs for more conditions

beginner

Can SUMIF and SUMIFS handle text conditions? Give an example.

Yes, they can. For example, SUMIF(A2:A10, "Apples", B2:B10) adds values in B2:B10 where A2:A10 equals "Apples."

What will SUMIF(A2:A10, ">5", B2:B10) do?

A Add values in A2:A10 where B2:B10 is greater than 5

B Add values in B2:B10 where A2:A10 is greater than 5

C Add all values in B2:B10

D Add all values in A2:A10

SUMIF checks the range A2:A10 for values greater than 5 and adds corresponding values from B2:B10.

Which function allows multiple conditions to sum values?

A SUMIF

B COUNTIF

C SUM

D SUMIFS

SUMIFS lets you add numbers based on more than one condition.

In SUMIFS, what is the first argument?

A Sum range

B Criteria 1

C Criteria range 1

D Range to check condition

The first argument in SUMIFS is the sum range, the cells to add.

What happens if you omit sum_range in SUMIF?

A Excel adds the cells in the range argument

B Excel returns an error

C Excel adds all numbers in the sheet

D Excel ignores the criteria

If sum_range is missing, SUMIF adds the cells in the range argument that meet the criteria.

Which of these is a valid SUMIF criteria to find cells equal to "Banana"?

A <>Banana

B >Banana

C "Banana"

D Banana

Criteria must be in quotes for text, so "Banana" is correct.

Explain how to use SUMIF to add sales amounts only for a specific product.

Think about which cells hold product names and which hold sales numbers.

Describe a situation where SUMIFS is better than SUMIF and how to write the formula.

Use an example like summing sales for a product in a certain region.

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