SUMIF and SUMIFS in Google Sheets - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the SUMIF function do in Google Sheets?

SUMIF adds up numbers in a range that meet a single condition you set. For example, it can add all sales over $100.

beginner

How is SUMIFS different from SUMIF?

SUMIFS adds numbers based on multiple conditions, while SUMIF uses only one condition. Use SUMIFS when you want to check more than one rule.

beginner

Write the syntax of the SUMIF function.

SUMIF(range, criterion, [sum_range])<br> - range: cells to check the condition<br> - criterion: the condition to meet<br> - sum_range: cells to add if condition is true (optional)

intermediate

What happens if you omit the sum_range in SUMIF?

If sum_range is left out, Google Sheets adds the cells in the range that meet the condition instead of a different set of cells.

intermediate

Give an example of a SUMIFS formula with two conditions.

Example: =SUMIFS(C2:C10, A2:A10, ">=2023-01-01", B2:B10, "Apples")<br>This adds values in C2:C10 where dates in A2:A10 are on or after Jan 1, 2023, and B2:B10 equals "Apples".

What does the formula =SUMIF(A1:A5, ">10") do?

A Adds numbers in A1:A5 greater than 10

B Adds numbers in A1:A5 less than 10

C Counts how many numbers are greater than 10

D Adds all numbers in A1:A5

SUMIF adds only the numbers in the range that are greater than 10.

Which function lets you add numbers based on multiple conditions?

A SUMIF

B COUNTIF

C SUM

D SUMIFS

SUMIFS allows multiple conditions, SUMIF only one.

In SUMIF(range, criterion, sum_range), what is sum_range?

A The cells to add if condition is true

B The condition to meet

C The cells to check the condition

D The total sum of all cells

sum_range is the range of cells to add when the condition is met.

What will =SUMIFS(B2:B10, A2:A10, "=Red", C2:C10, ">5") do?

A Count how many cells are Red

B Add B2:B10 where A2:A10 is Red or C2:C10 is greater than 5

C Add B2:B10 where A2:A10 is Red and C2:C10 is greater than 5

D Add all B2:B10 values

SUMIFS adds values where all conditions are true.

If you want to add sales only for "Apples", which formula is correct?

A =SUMIF(C2:C10, "Apples", B2:B10)

B =SUMIF(B2:B10, "Apples", C2:C10)

C =SUMIF(B2:B10, ">Apples", C2:C10)

D =SUMIF(C2:C10, ">Apples")

You check B2:B10 for "Apples" and sum corresponding C2:C10 values.

Explain how to use SUMIF to add values based on one condition. Include an example.

Think about adding sales only for a specific product.

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

Consider adding sales for a product in a specific month.