Excel Formulas and Data Analysis Course: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS
Temaquiz utan tidsgräns: 15 frågor om Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, med en förklaring efter varje svar. Frågor och svar blandas varje gång.
Temaquiz 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS
Fråga 1 av 15
Visa svar
Svar: C. sum_range, the cells to add
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.
Fråga 2 av 15
Visa svar
Svar: 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.
Fråga 3 av 15
Visa svar
Svar: A. 2610
It adds the Amount for every row where Rep is Ana: 1,200 + 510 + 900 = 2,610.
Fråga 4 av 15
Visa svar
Svar: D. 2
COUNTIFS counts rows where every condition is met. Two rows are both North and Alarms (rows 4 and 10).
Fråga 5 av 15
Visa svar
Svar: B. 2100
Only rows that are North AND Cameras count: 1,200 + 900 = 2,100.
Fråga 6 av 15
Visa svar
Svar: 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.
Fråga 7 av 15
Visa svar
Svar: 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.
Fråga 8 av 15
Visa svar
Svar: B. 983.33
Ben has three sales: (850 + 1,500 + 600) / 3 = 983.33.
Fråga 9 av 15
Visa svar
Svar: C. #DIV/0!
No row is in the West region. When no cells meet all the criteria, AVERAGEIFS returns #DIV/0!.
Fråga 10 av 15
Visa svar
Svar: 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).
Fråga 11 av 15
Visa svar
Svar: 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.
Fråga 12 av 15
Visa svar
Svar: C. 2520
It adds amounts dated March 9 to March 12, 2026, including both ends: 900 + 680 + 340 + 600 = 2,520.
Fråga 13 av 15
Visa svar
Svar: 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.
Fråga 14 av 15
Visa svar
Svar: 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.
Fråga 15 av 15
Visa svar
Svar: 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.
Tangentbord: 1–4 för att svara, Enter för nästa fråga.