Excel Formulas and Data Analysis Course: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS
Untimed topic quiz: 15 questions on Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, with an explanation after each answer. Questions and answers are shuffled every time.
Topic quiz 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS
Question 1 of 15
Show answer
Answer: C. sum_range, the cells to add
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.
Question 2 of 15
Show answer
Answer: C. Third, after the criteria
SUMIF is SUMIF(range, criteria, [sum_range]). Microsoft points out that sum_range is first in SUMIFS but third in SUMIF.
Question 3 of 15
Show answer
Answer: A. 2610
It adds the Amount for every row where Rep is Ana: 1,200 + 510 + 900 = 2,610.
Question 4 of 15
Show answer
Answer: D. 2
COUNTIFS counts rows where every condition is met. Two rows are both North and Alarms (rows 4 and 10).
Question 5 of 15
Show answer
Answer: B. 2100
Only rows that are North AND Cameras count: 1,200 + 900 = 2,100.
Question 6 of 15
Show answer
Answer: B. 42
"<>Sensors" means "not equal to Sensors". It adds the units of the other eight rows: 4+10+6+2+5+3+8+4 = 42.
Question 7 of 15
Show answer
Answer: B. 7
Seven amounts are 600 or more (1,200, 850, 600, 1,500, 900, 680 and 600). The two 600s count because >= includes the value itself.
Question 8 of 15
Show answer
Answer: B. 983.33
Ben has three sales: (850 + 1,500 + 600) / 3 = 983.33.
Question 9 of 15
Show answer
Answer: C. #DIV/0!
No row is in the West region. When no cells meet all the criteria, AVERAGEIFS returns #DIV/0!.
Question 10 of 15
Show answer
Answer: D. 5
A question mark matches exactly one character, so "?e?" matches three-letter names with e in the middle: Ben (3 rows) and Dev (2 rows).
Question 11 of 15
Show answer
Answer: C. =COUNTIFS(F2:F11,">"&H2)
Put the operator in quotes and join it to the cell with &. Inside quotes, ">H2" is read as the text H2, not the cell.
Question 12 of 15
Show answer
Answer: C. 2520
It adds amounts dated March 9 to March 12, 2026, including both ends: 900 + 680 + 340 + 600 = 2,520.
Question 13 of 15
Show answer
Answer: A. Same number of rows and columns as the first range
Each extra range must be the same shape as criteria_range1, so Excel can compare the cells row by row.
Question 14 of 15
Show answer
Answer: A. 0
SUMIFS adds a row only when ALL conditions are true. No cell can be North and East at once, so the result is 0. To add both regions, add two SUMIFS together.
Question 15 of 15
Show answer
Answer: C. 4.33
North rows with an amount over 400 are Ana's 1,200, 510 and 900 sales. Their units are 4, 6 and 3, so the average is 13 / 3 = 4.33.
Keyboard: 1–4 to answer, Enter for the next question.