# www.TestsTestsTests.com

## Microsoft Excel FREE Resources Index Page

Free Excel Tests – Using IF, SUMIF and COUNTIF Test – Excel 2010 – Formulas and Functions

# Using IF, SUMIF and COUNTIF Test

## Free Online Microsoft Excel Tests Excel 2010 – Formulas and Functions

### Using IF, SUMIF and COUNTIF Test

1) How do you insert an IF, COUNTIF or SUMIF function in a cell?
a) Click on the Insert tab on the Ribbon and select the relevant function from the Functions group.
b) Type an equal sign in a cell followed by the name of the function and relevant arguments, for example =IF(A1=2,”Yes”,”No”)
c) Right-click a cell and select the relevant function from the menu list to insert.
d) All of the options listed above are correct.

2) Study the screenshot above.  Which of the following functions, when inserted in the highlighted cell (C2) above, will return the word “yes” ?
a) =IF(B2>50,”yes”)
b) =IF(C2=”yes”)
c) =IF(B2=C2, “yes”)
d) =IF(B2=50, “yes”)

3) Which of the following is a comparison operator for inserting a not equal to argument in an IF, COUNTIF or SUMIF function?
a) <=
b) <>
c) >=
d) ><

4) Which of the following represent the correct arguments for the SUMIF function?
a) =SUMIF(criteria, range, criteria_range)
b) =SUMIF(criteria_range, true, false)
c) =SUMIF(range, criteria, [sum_range])
d) =SUMIF(range, sum_range, [criteria])

5) Study the screenshot above.  Which of the formulas in the options listed below, when entered into the cell highlighted (E2) in yellow in this screenshot, will add a Late Penalty of \$500 to the student’s Owing amount, if they still owe \$5,000 or more on their yearly fees?  This formula can then be copied down to the cells below the highlighted cell.
a) =SUMIF(D2>=5000,D2+500)
b) =IF(>=5000,D2=500)
c) I=SUMIF(D2>=5000,500)
d) =IF(D2>=5000,D2+500)

6) Study the screenshot above.  Which of the following options represent a formula or function that is entered in the highlighted cells (E2 to E4) to count the total enrollments for each module?
a) =IF(B2:B13=”Biology”,”yes”,”no”)
b) =COUNTIF(\$B\$2:\$B\$13,D2)
c) =COUNTIF(B2:B13=”Biology”)
d) None of the options listed above are correct.

7) Study the screenshot above.  James is trying to calculate the total cost of usage for residents who have a high electricity bill of over \$5000 in the Total Usage column.  The formula he entered in cell N3 (Total High Usage)(see the formula bar) keeps returning an error message.  Can you spot what is wrong in this formula?
a) He should have used an IF not a SUMIF function as follows: IF(K2:K17>5000).
b) There must not be a comma between the reference cells and the criteria as follows: SUMIF(K2:K17>5000)
c) The criteria should be enclosed in quotation marks as follows: =SUMIF(K2:K17,”>5000″)
d) All of the options listed above are valid corrections.

8) Which of the following is the correct formula syntax for using the COUNTIF function?
a) =COUNTIF(criteria, range)
b) =COUNTIF(criteria, range, [count_range])
c) =COUNT(IF(range, criteria))
d) =COUNTIF(range, criteria)

9) Study the formula in the screenshot above.  What value will this function return if the condition is NOT met (see the formula bar)?
a) It will leave the cell blank.
b) It will insert quotation marks in the cell.
c) It will insert \$8,240 in the cell.
d) It will insert \$500 in the cell.

10) Which of the following options represent the correct formula syntax for the IF function?
a) =IF(range, criteria, value_if_true, [value_if_false])
b) =FUNCTION:IF((logical_test,[value_if_true], [value_if_false]))
c) =IF(logical_test,[value_if_true], [value_if_false])
d) =IF(range, criteria_if_true, criteria_if_false)