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

1. السؤال 1 من 15

   In SUMIFS, which argument comes first?

   • AThe number of conditions to check
   • Bcriteria1, the first condition
   • Csum_range, the cells to add
   • Dcriteria_range1, the first range to test
   أظهر الإجابة

   الإجابة: C. sum_range, the cells to add

   The syntax is SUMIFS(sum_range, criteria_range1, criteria1, ...). The cells to add always come first.

2. السؤال 2 من 15

   In SUMIF (with one condition), where does the range to add up go?

   • ASUMIF has no sum_range argument
   • BSecond, between range and criteria
   • CThird, after the criteria
   • DFirst, the same as in SUMIFS
   أظهر الإجابة

   الإجابة: 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.

3. السؤال 3 من 15

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

   	A	B	C	D	E	F
   1	التاريخ	Rep	المنطقة	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

   • A2610
   • B1200
   • C13
   • D3
   أظهر الإجابة

   الإجابة: A. 2610

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

4. السؤال 4 من 15

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

   	A	B	C	D	E	F
   1	التاريخ	Rep	المنطقة	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

   • A4
   • B5
   • C850
   • D2
   أظهر الإجابة

   الإجابة: D. 2

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

5. السؤال 5 من 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	التاريخ	Rep	المنطقة	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

   • A3310
   • B2100
   • C4200
   • D1200
   أظهر الإجابة

   الإجابة: B. 2100

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

6. السؤال 6 من 15

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

   	A	B	C	D	E	F
   1	التاريخ	Rep	المنطقة	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

   • A32
   • B42
   • C74
   • D7
   أظهر الإجابة

   الإجابة: 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.

7. السؤال 7 من 15

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

   	A	B	C	D	E	F
   1	التاريخ	Rep	المنطقة	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

   • A6200
   • B7
   • C3
   • D5
   أظهر الإجابة

   الإجابة: 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.

8. السؤال 8 من 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	التاريخ	Rep	المنطقة	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

   • A2950
   • B983.33
   • C1475
   • D754
   أظهر الإجابة

   الإجابة: B. 983.33

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

9. السؤال 9 من 15

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

   	A	B	C	D	E	F
   1	التاريخ	Rep	المنطقة	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

   • A#VALUE!
   • B0
   • C#DIV/0!
   • D#N/A
   أظهر الإجابة

   الإجابة: C. #DIV/0!

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

10. السؤال 10 من 15

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

    	A	B	C	D	E	F
    1	التاريخ	Rep	المنطقة	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

    • A0
    • B3
    • C10
    • D5
    أظهر الإجابة

    الإجابة: 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).

11. السؤال 11 من 15

    Cell H2 holds a target amount. Which formula counts the amounts in F2:F11 that are greater than the target?

    • A=COUNTIFS(F2:F11,>H2)
    • B=COUNTIFS(">"&H2,F2:F11)
    • C=COUNTIFS(F2:F11,">"&H2)
    • D=COUNTIFS(F2:F11,">H2")
    أظهر الإجابة

    الإجابة: 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.

12. السؤال 12 من 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	التاريخ	Rep	المنطقة	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

    • A1920
    • B4
    • C2520
    • D3120
    أظهر الإجابة

    الإجابة: C. 2520

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

13. السؤال 13 من 15

    What must be true of every criteria range in COUNTIFS?

    • ASame number of rows and columns as the first range
    • BFilled only with numbers, never with text
    • CSorted from A to Z before you use it
    • DStored on a separate worksheet each time
    أظهر الإجابة

    الإجابة: 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.

14. السؤال 14 من 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	التاريخ	Rep	المنطقة	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

    • A0
    • B4590
    • C#VALUE!
    • D3310
    أظهر الإجابة

    الإجابة: 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.

15. السؤال 15 من 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	التاريخ	Rep	المنطقة	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

    • A6
    • B5.8
    • C4.33
    • D13
    أظهر الإجابة

    الإجابة: 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.

لوحة المفاتيح: 1–4 للإجابة، وEnter للسؤال التالي.