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

Quiz thématique sans limite de temps : 15 questions sur Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, avec une explication après chaque réponse. Les questions et les réponses sont mélangées à chaque fois.

Formation
Canada
96 questions
Vérifié le 13 octobre 2026
Vérifié (faits) le 13 octobre 2026

Commencez par les leçonsExcel Formulas and Data Analysis Course: Lessons : environ 240 minutes de lecture. Chaque question du test est traitée dans les leçons.

Quiz thématique 2 : Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Question 1 sur 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
Voir la réponse
Réponse: 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 sur 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
Voir la réponse
Réponse: 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 sur 15

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

	A	B	C	D	E	F
1	Date	Rep	Région	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

Voir la réponse

Réponse: A. 2610

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

Question 4 sur 15

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

	A	B	C	D	E	F
1	Date	Rep	Région	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

Voir la réponse

Réponse: D. 2

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

Question 5 sur 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	Date	Rep	Région	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

Voir la réponse

Réponse: B. 2100

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

Question 6 sur 15

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

	A	B	C	D	E	F
1	Date	Rep	Région	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

Voir la réponse

Réponse: 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 sur 15

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

	A	B	C	D	E	F
1	Date	Rep	Région	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

Voir la réponse

Réponse: 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 sur 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	Date	Rep	Région	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

Voir la réponse

Réponse: B. 983.33

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

Question 9 sur 15

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

	A	B	C	D	E	F
1	Date	Rep	Région	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

Voir la réponse

Réponse: C. #DIV/0!

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

Question 10 sur 15

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

	A	B	C	D	E	F
1	Date	Rep	Région	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

Voir la réponse

Réponse: 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 sur 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")
Voir la réponse
Réponse: 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 sur 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	Date	Rep	Région	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

Voir la réponse

Réponse: C. 2520

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

Question 13 sur 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
Voir la réponse
Réponse: 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 sur 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	Date	Rep	Région	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

Voir la réponse

Réponse: 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 sur 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	Date	Rep	Région	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

Voir la réponse

Réponse: 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.

Quiz thématique 2 : Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Tests associés