Font size
WorksheetsExcel - Conditional Functions
Total questions: 10
Worksheet time: 14mins
Full Name
Student Id (ID No.)
Email ID
CountA function can count on the following:
Numbers
Text
Blank Cells
Non-blank Cell
Which of the following is the syntax for Averageifs?
Average_range, criteria_range1, criteria,...
Criteria_range1, Criteria, Average_range,....
Criteria, Criteria_range1, Average_range,...
Criteria_range1, Average_range, Criteria,..
John wishes to count all the products that have been marked with a Yes in the Expired column B5:B9) AND under the Stored column (C5:C9) is marked as Refrigerator. Which of the functions below will permit him to calculate this when entered into cell B11 (the highlighted cell in the screenshot alongside)?
=COUNTIF(B5:B9,”Yes”,C5:C9,”Refrigerator”)
=SUMIFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)
=IFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)
=COUNTIFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)
Which formula shall yield us the average income of employees in Ohio?
=averageif(C2:C10, "OHIO", C2:C10)
=averageif("Ohio", C2:C10, A2:A10)
=averageif(A2:A10, "Ohio", C2:C10)
=averageifs(A2:A10, "Ohio", C2:C10)
The adjacent table gives the details of revenue by account numbers. Which formula shall return the total revenue for "Gold" accounts in the state of NY?
=total(D2:D11, "Gold", "NY")
=sumif(D2:D11, Type, "Gold", State, "NY")
=Sumifs(Revenue, Type, "Gold", State, "NY")
=SumTotal(D2:D11, "Gold", "NY")
Which formula will you use to calculate the Number of people who were absent?
=COUNTBLANK(Attendance)
=SUM(Attendance)
=COUNTA(Attendance)
=COUNT("Attendance")
What is the difference between Sum and Sumifs?
Sum only calculates the total, Sumifs returns the sum of cells which meet the multiple criteria
Sumifs is used for adding numbers in different columns, while Sum is used for adding numbers in the same column.
Sumifs requires more parameters than Sum.
Sumifs is used for calculating the average, while Sum is used for calculating the total.
