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

Prueba temática sin límite de tiempo: 15 preguntas sobre Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, con una explicación después de cada respuesta. Las preguntas y respuestas se mezclan cada vez.

Curso
Canadá
96 preguntas
Revisado el 13 de octubre de 2026
Verificado el 13 de octubre de 2026

Empieza por las leccionesExcel Formulas and Data Analysis Course: Lessons: unos 240 minutos de lectura. Todas las preguntas del test se tratan en las lecciones.

Prueba temática 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Pregunta 1 de 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
Ver respuesta
Respuesta: C. sum_range, the cells to add
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.
Pregunta 2 de 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
Ver respuesta
Respuesta: 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.

Pregunta 3 de 15

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

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

Ver respuesta

Respuesta: A. 2610

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

Pregunta 4 de 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	Region	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

Ver respuesta

Respuesta: D. 2

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

Pregunta 5 de 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	Region	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

Ver respuesta

Respuesta: B. 2100

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

Pregunta 6 de 15

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

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

Ver respuesta

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

Pregunta 7 de 15

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

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

Ver respuesta

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

Pregunta 8 de 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	Region	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

Ver respuesta

Respuesta: B. 983.33

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

Pregunta 9 de 15

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

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

Ver respuesta

Respuesta: C. #DIV/0!

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

Pregunta 10 de 15

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

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

Ver respuesta

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

Pregunta 11 de 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")
Ver respuesta
Respuesta: 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.

Pregunta 12 de 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	Region	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

Ver respuesta

Respuesta: C. 2520

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

Pregunta 13 de 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
Ver respuesta
Respuesta: 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.

Pregunta 14 de 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	Region	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

Ver respuesta

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

Pregunta 15 de 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	Region	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

Ver respuesta

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

Prueba temática 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Exámenes relacionados