# www.TestsTestsTests.com

## Microsoft Excel FREE Resources Index Page

Free Excel Tests – NESTED IFs, SUMIFs & COUNTIFs in Functions Advanced Functions Test – Excel 2010 – Formulas and Functions

# Using Nested IFs, SUMIFs & COUNTIFs in Functions Advanced Test

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

### Using Nested IFs, SUMIFs & COUNTIFs in Functions (Advanced) Test

1) What is a nested IF statement?
a) It is an IF statement that has been inserted into a cell.
b) It is an IF statement that has been inserted into a graphic such as a shape.
c) It is an IF statement that has been inserted into another IF statement.
d) None of the options listed above are correct.

2) Study the screenshot above.  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 above)?
a) =COUNTIFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)
b) =SUMIFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)
c) =IFS(B5:B9,”Yes”,C5:C9,”Refrigerator”)
d) =COUNTIF(B5:B9,”Yes”,C5:C9,”Refrigerator”)

3) Which of the following is NOT an argument you will find in a SUMIFs function?
a) sum_range
b) criteria_range1
c) value_if_true
d) criteria2

4) Which of the following statements are TRUE?
a) You use SUMIFs to count cells based on specified ranges and criteria.
b) You have to first use an IF statement in a worksheet before you can use SUMIFs or COUNTIFs.
c) You use COUNTIFs to count the number of cells specified by a given set of conditions or criteria.
d) None of the options listed above are correct.

5) Which of the following nested IF functions are CORRECT?
a) =IF(B5=”Shirt”,IF(A5=”large”,20,10),”Unavailable”)
b) =IFS(B5=”Shirt”,IF(A5=”large”,20,10),”Unavailable”)
c) =IF(B5=”Shirt”,=IF(A5=”large”,20,10),”Unavailable”)
d) =IF:(B5=”Shirt”,IF(A5=”large”,20,10),”Unavailable”)

6) Which of the following is the correct argument(s)/syntax for the COUNTIFs function?
a) =COUNT(IFs(criteria_range, criteria1, criteria_range2, criteria2)
b) COUNTIFs=(criteria_range, criteria1, criteria_range2, criteria2)
c) =COUNTIFs(criteria_range, criteria1, criteria_range2, criteria2)
d) =COUNTIFs(criteria1, criteria2, criteria3)

7) Study the screenshot above.  In order to create a formula that will add up how many rooms there are that are both available (has the word No in the Booked column) and sleeps 2 or more (value in the Sleeps column).  Which function could we use?
a) SUMIF
b) SUMIFs
c) COUNTIFs
d) All of the above

8) Study the screenshot above.  What do you think will be the result displayed in the highlighted cell (D2) if the following formula is entered into it:  =IF(B2=”Solicitor”,IF(C2=”Yes”, “Yes”,”No”),”Not Applicable”)
a) Yes
b) Not Applicable
c) No
d) Solicitor

9) Paul entered the following formula into a cell but it keeps popping up with an error.  The formula he entered is: =IF(B9>500,IF(F9=”Yes”,B9*B20,B9*B21)”Empty”).  Which of the following is the corrected version?
a) =IF(B9>500,IF(F9=”Yes”,B9*B20,B9*B21),”Empty”) – the comma before the last argument was missing.
b) =IF(B9>500,IF(F9,=”Yes”,B9*B20,B9*B21)”Empty”) – the comma after F9 in the nested IF was missing.
c) =IF(B9>500,=IF(F9=”Yes”,B9*B20,B9*B21)”Empty”) – the equal sign before the nested IF was missing.
d) None of the options listed above are correct.

10) James needs to add together various payment amounts in a worksheet depending on three different criteria.  Which function would be the most efficient for him to use?
a) COUNTIFs
b) SUMIFs
c) SUMIF
d) None of the above.