SUMIF and SUMIFS in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to sum values in column B where column A equals "Apple".

Excel

=SUMIF(A:A, [1], B:B)

Drag options to blanks, or click blank then click option'

A"Apple"

BApple

CA1

D"A"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to put quotes around text criteria.

Using a cell reference instead of the text string.

The SUMIF function needs the criteria as a text string, so it must be enclosed in quotes.

2fill in blank

medium

Complete the formula to sum values in column C where column B is greater than 100.

Excel

=SUMIF(B:B, [1], C:C)

Drag options to blanks, or click blank then click option'

A">100"

B100

C>=100

D"100"

Attempts:

3 left

💡 Hint

Common Mistakes

Using just 100 without an operator.

Putting the number 100 without quotes.

The criteria must be a string with the comparison operator and number, like ">100".

3fill in blank

hard

Fix the error in the formula to sum values in column D where column A equals "Banana" and column B is less than 50.

Excel

=SUMIFS(D:D, A:A, [1], B:B, [2])

Drag options to blanks, or click blank then click option'

A"Banana"

BBanana

C"<50"

D<50

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around text or comparison criteria.

Using raw numbers or text without quotes.

Text criteria like "Banana" must be in quotes. Also, comparison criteria like "<50" must be in quotes.

4fill in blank

hard

Fill both blanks to sum values in column E where column C equals "Yes" and column D is not equal to 0.

Excel

=SUMIFS(E:E, C:C, [1], D:D, [2])

Drag options to blanks, or click blank then click option'

A"Yes"

B"No"

C"<>0"

D"=0"

Attempts:

3 left

💡 Hint

Common Mistakes

Using "No" instead of "Yes".

Using "=0" instead of "<>0".

Use "Yes" to match text in column C and "<>0" to exclude zeros in column D.

5fill in blank

hard

Fill all three blanks to sum values in column F where column A equals "Orange", column B is greater than 20, and column C equals "Complete".

Excel

=SUMIFS(F:F, A:A, [1], B:B, [2], C:C, [3])

Drag options to blanks, or click blank then click option'

A"Orange"

B">20"

C"Complete"

D"Incomplete"

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes around text or comparison criteria.

Using wrong comparison operators.

Use "Orange" and "Complete" as text criteria, and ">20" for the numeric comparison.