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

Quiz tematico senza limite di tempo: 15 domande su Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, con una spiegazione dopo ogni risposta. Domande e risposte vengono mescolate ogni volta.

Corso
Canada
96 domande
Verificato: 13 ottobre 2026
Verificato il 13 ottobre 2026

Inizia dalle lezioniExcel Formulas and Data Analysis Course: Lessons: circa 240 minuti di lettura. Ogni domanda del test è trattata nelle lezioni.

Quiz tematico 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Domanda 1 di 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
Mostra la risposta
Risposta: C. sum_range, the cells to add
The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.
Domanda 2 di 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
Mostra la risposta
Risposta: 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.

Domanda 3 di 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

Mostra la risposta

Risposta: A. 2610

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

Domanda 4 di 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

Mostra la risposta

Risposta: D. 2

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

Domanda 5 di 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

Mostra la risposta

Risposta: B. 2100

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

Domanda 6 di 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

Mostra la risposta

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

Domanda 7 di 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

Mostra la risposta

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

Domanda 8 di 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

Mostra la risposta

Risposta: B. 983.33

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

Domanda 9 di 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

Mostra la risposta

Risposta: C. #DIV/0!

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

Domanda 10 di 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

Mostra la risposta

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

Domanda 11 di 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")
Mostra la risposta
Risposta: 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.

Domanda 12 di 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

Mostra la risposta

Risposta: C. 2520

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

Domanda 13 di 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
Mostra la risposta
Risposta: 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.

Domanda 14 di 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

Mostra la risposta

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

Domanda 15 di 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

Mostra la risposta

Risposta: 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 tematico 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

Test correlati