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

Questionário temático sem limite de tempo: 15 perguntas sobre Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, com uma explicação depois de cada resposta. As perguntas e respostas são baralhadas de cada vez.

Curso
Canadá
96 perguntas
Verificado em 13 de outubro de 2026
Verificado em 13 de outubro de 2026

Comece pelas liçõesExcel Formulas and Data Analysis Course: Lessons: cerca de 240 minutos de leitura. Todas as perguntas do teste são abordadas nas lições.

Questionário temático 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Pergunta 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
Mostrar resposta
Resposta: C. sum_range, the cells to add
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.
Pergunta 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
Mostrar resposta
Resposta: 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.

Pergunta 3 de 15

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

	A	B	C	D	E	F
1	Data	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

Mostrar resposta

Resposta: A. 2610

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

Pergunta 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	Data	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

Mostrar resposta

Resposta: D. 2

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

Pergunta 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	Data	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

Mostrar resposta

Resposta: B. 2100

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

Pergunta 6 de 15

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

	A	B	C	D	E	F
1	Data	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

Mostrar resposta

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

Pergunta 7 de 15

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

	A	B	C	D	E	F
1	Data	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

Mostrar resposta

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

Pergunta 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	Data	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

Mostrar resposta

Resposta: B. 983.33

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

Pergunta 9 de 15

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

	A	B	C	D	E	F
1	Data	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

Mostrar resposta

Resposta: C. #DIV/0!

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

Pergunta 10 de 15

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

	A	B	C	D	E	F
1	Data	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

Mostrar resposta

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

Pergunta 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")
Mostrar resposta
Resposta: 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.

Pergunta 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	Data	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

Mostrar resposta

Resposta: C. 2520

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

Pergunta 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
Mostrar resposta
Resposta: 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.

Pergunta 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	Data	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

Mostrar resposta

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

Pergunta 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	Data	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

Mostrar resposta

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

Questionário temático 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Testes relacionados