Excel Formulas and Data Analysis Course: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Themaquiz zonder tijdslimiet: 15 vragen over Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, met een uitleg na elk antwoord. Vragen en antwoorden worden elke keer gehusseld.

Cursus
Canada
96 vragen
Gecontroleerd op 13 oktober 2026
Feiten gecontroleerd op 13 oktober 2026

Begin met de lessenExcel Formulas and Data Analysis Course: Lessons: ongeveer 240 minuten leestijd. Elke testvraag wordt in de lessen behandeld.

Themaquiz 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Vraag 1 van 15
In SUMIFS, which argument comes first?
- The number of conditions to check
- criteria1, the first condition
- sum_range, the cells to add
- criteria_range1, the first range to test
Toon antwoord
Antwoord: C. sum_range, the cells to add
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.
Vraag 2 van 15
In SUMIF (with one condition), where does the range to add up go?
- SUMIF has no sum_range argument
- Second, between range and criteria
- Third, after the criteria
- First, the same as in SUMIFS
Toon antwoord
Antwoord: 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.

Vraag 3 van 15

In the sales sheet below, what does =SUMIFS(F2:F11,B2:B11,"Ana") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

2610
1200
13
3

Toon antwoord

Antwoord: A. 2610

It adds the Amount for every row where Rep is Ana: 1,200 + 510 + 900 = 2,610.

Vraag 4 van 15

In the sales sheet below, what does =COUNTIFS(C2:C11,"North",D2:D11,"Alarms") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

Toon antwoord

Antwoord: D. 2

COUNTIFS counts rows where every condition is met. Two rows are both North and Alarms (rows 4 and 10).

Vraag 5 van 15

In the sales sheet below, what does =SUMIFS(F2:F11,C2:C11,"North",D2:D11,"Cameras") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

3310
2100
4200
1200

Toon antwoord

Antwoord: B. 2100

Only rows that are North AND Cameras count: 1,200 + 900 = 2,100.

Vraag 6 van 15

In the sales sheet below, what does =SUMIFS(E2:E11,D2:D11,"<>Sensors") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

Toon antwoord

Antwoord: 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.

Vraag 7 van 15

In the sales sheet below, what does =COUNTIFS(F2:F11,">=600") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

6200
7
3
5

Toon antwoord

Antwoord: 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.

Vraag 8 van 15

In the sales sheet below, what does =AVERAGEIFS(F2:F11,B2:B11,"Ben") return (to 2 decimal places)?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

2950
983.33
1475
754

Toon antwoord

Antwoord: B. 983.33

Ben has three sales: (850 + 1,500 + 600) / 3 = 983.33.

Vraag 9 van 15

In the sales sheet below, what does =AVERAGEIFS(F2:F11,C2:C11,"West") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

#VALUE!
0
#DIV/0!
#N/A

Toon antwoord

Antwoord: C. #DIV/0!

No row is in the West region. When no cells meet all the criteria, AVERAGEIFS returns #DIV/0!.

Vraag 10 van 15

In the sales sheet below, what does =COUNTIFS(B2:B11,"?e?") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

Toon antwoord

Antwoord: 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).

Vraag 11 van 15
Cell H2 holds a target amount. Which formula counts the amounts in F2:F11 that are greater than the target?
- =COUNTIFS(F2:F11,>H2)
- =COUNTIFS(">"&H2,F2:F11)
- =COUNTIFS(F2:F11,">"&H2)
- =COUNTIFS(F2:F11,">H2")
Toon antwoord
Antwoord: 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.

Vraag 12 van 15

Column A of the sales sheet below holds real dates. What does =SUMIFS(F2:F11,A2:A11,">="&DATE(2026,3,9),A2:A11,"<="&DATE(2026,3,12)) return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

1920
4
2520
3120

Toon antwoord

Antwoord: C. 2520

It adds amounts dated March 9 to March 12, 2026, including both ends: 900 + 680 + 340 + 600 = 2,520.

Vraag 13 van 15
What must be true of every criteria range in COUNTIFS?
- Same number of rows and columns as the first range
- Filled only with numbers, never with text
- Sorted from A to Z before you use it
- Stored on a separate worksheet each time
Toon antwoord
Antwoord: 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.

Vraag 14 van 15

In the sales sheet below, what does =SUMIFS(F2:F11,C2:C11,"North",C2:C11,"East") return?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

0
4590
#VALUE!
3310

Toon antwoord

Antwoord: 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.

Vraag 15 van 15

In the sales sheet below, what does =AVERAGEIFS(E2:E11,C2:C11,"North",F2:F11,">400") return (to 2 decimal places)?

	A	B	C	D	E	F
1	Datum	Rep	Gewest	Product	Units	Amount
2	2026-03-02	Ana	North	Cameras	4	1200
3	2026-03-02	Ben	South	Alarms	10	850
4	2026-03-03	Ana	North	Alarms	6	510
5	2026-03-04	Chloé	East	Cameras	2	600
6	2026-03-05	Ben	South	Cameras	5	1500
7	2026-03-06	Dev	North	Sensors	12	360
8	2026-03-09	Ana	North	Cameras	3	900
9	2026-03-10	Chloé	East	Alarms	8	680
10	2026-03-11	Dev	North	Alarms	4	340
11	2026-03-12	Ben	South	Sensors	20	600

6
5.8
4.33
13

Toon antwoord

Antwoord: 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.

Themaquiz 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Gerelateerde examens