PassEveryTest
Tagalog

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

Quiz sa paksa na walang limitasyon sa oras: 15 tanong tungkol sa Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS, may paliwanag pagkatapos ng bawat sagot. Binabago ang ayos ng mga tanong at sagot sa bawat pagkakataon.

  • Kurso
  • Canada
  • 96 tanong
  • Sinuri noong Oktubre 13, 2026
  • Na-fact-check noong Oktubre 13, 2026
Magsimula sa mga aralinExcel Formulas and Data Analysis Course: Lessons: mga 240 minutong pagbasa. Saklaw ng mga aralin ang bawat tanong sa pagsusulit.

Quiz sa paksa 2: Conditional totals: SUMIFS, COUNTIFS and AVERAGEIFS

  1. Tanong 1 ng 15

    In SUMIFS, which argument comes first?
    Ipakita ang sagot

    Sagot: C. sum_range, the cells to add

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

  2. Tanong 2 ng 15

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

    Sagot: 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. Tanong 3 ng 15

    In the sales sheet below, what does =SUMIFS(F2:F11,B2:B11,"Ana") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: A. 2610

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

  4. Tanong 4 ng 15

    In the sales sheet below, what does =COUNTIFS(C2:C11,"North",D2:D11,"Alarms") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: D. 2

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

  5. Tanong 5 ng 15

    In the sales sheet below, what does =SUMIFS(F2:F11,C2:C11,"North",D2:D11,"Cameras") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: B. 2100

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

  6. Tanong 6 ng 15

    In the sales sheet below, what does =SUMIFS(E2:E11,D2:D11,"<>Sensors") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: 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. Tanong 7 ng 15

    In the sales sheet below, what does =COUNTIFS(F2:F11,">=600") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: 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. Tanong 8 ng 15

    In the sales sheet below, what does =AVERAGEIFS(F2:F11,B2:B11,"Ben") return (to 2 decimal places)?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: B. 983.33

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

  9. Tanong 9 ng 15

    In the sales sheet below, what does =AVERAGEIFS(F2:F11,C2:C11,"West") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: C. #DIV/0!

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

  10. Tanong 10 ng 15

    In the sales sheet below, what does =COUNTIFS(B2:B11,"?e?") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: 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. Tanong 11 ng 15

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

    Sagot: 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. Tanong 12 ng 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?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: C. 2520

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

  13. Tanong 13 ng 15

    What must be true of every criteria range in COUNTIFS?
    Ipakita ang sagot

    Sagot: 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. Tanong 14 ng 15

    In the sales sheet below, what does =SUMIFS(F2:F11,C2:C11,"North",C2:C11,"East") return?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

    Sagot: 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. Tanong 15 ng 15

    In the sales sheet below, what does =AVERAGEIFS(E2:E11,C2:C11,"North",F2:F11,">400") return (to 2 decimal places)?
    ABCDEF
    1PetsaRepRehiyonProductUnitsAmount
    22026-03-02AnaNorthCameras41200
    32026-03-02BenSouthAlarms10850
    42026-03-03AnaNorthAlarms6510
    52026-03-04ChloéEastCameras2600
    62026-03-05BenSouthCameras51500
    72026-03-06DevNorthSensors12360
    82026-03-09AnaNorthCameras3900
    92026-03-10ChloéEastAlarms8680
    102026-03-11DevNorthAlarms4340
    112026-03-12BenSouthSensors20600
    Ipakita ang sagot

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

Mga kaugnay na pagsusulit