专题测验2：Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS
第1题，共15题
显示答案
答案: C. sum_range, the cells to add
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.
第2题，共15题
显示答案
答案: 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.
第3题，共15题
显示答案
答案: A. 2610
It adds the Amount for every row where Rep is Ana: 1,200 + 510 + 900 = 2,610.
第4题，共15题
显示答案
答案: D. 2
COUNTIFS counts rows where every condition is met. Two rows are both North and Alarms (rows 4 and 10).
第5题，共15题
显示答案
答案: B. 2100
Only rows that are North AND Cameras count: 1,200 + 900 = 2,100.
第6题，共15题
显示答案
答案: 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.
第7题，共15题
显示答案
答案: 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.
第8题，共15题
显示答案
答案: B. 983.33
Ben has three sales: (850 + 1,500 + 600) / 3 = 983.33.
第9题，共15题
显示答案
答案: C. #DIV/0!
No row is in the West region. When no cells meet all the criteria, AVERAGEIFS returns #DIV/0!.
第10题，共15题
显示答案
答案: 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).
第11题，共15题
显示答案
答案: 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.
第12题，共15题
显示答案
答案: C. 2520
It adds amounts dated March 9 to March 12, 2026, including both ends: 900 + 680 + 340 + 600 = 2,520.
第13题，共15题
显示答案
答案: 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.
第14题，共15题
显示答案
答案: 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.
第15题，共15题
显示答案
答案: 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.