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])<br>• range: cells to check the condition<br>• criteria: condition to meet<br>• 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], ...)<br>• sum_range: cells to add<br>• criteria_range1: first range to check<br>• criteria1: first condition<br>• 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.