Fi6006: Week 2 – Criteria Based References & Data Validation

• In past classes we have used basic functions for calculation (SUM/AVERAGE)

• We have also created our own formulas
• The following notes will give you a step by step guide of how to perform a calculation based
off particular criteria

COUNTIFS Function
• The COUNT function calculates how many (number values) values are in a cell range
• COUNTA counts how many non-numerical values are in a cell range
• COUNTBLANK will count how many cells do not have a value in them
• COUNTIF/COUNTIFS will count how many values are in a range where a specific criterion is
applied
• COUNTIFS will allow choose more than one range. It is recommended that you use this
version of the function
• It is important to not confuse COUNT functions with SUM functions
• COUNT functions provide us with a result that shows how many cells in the range we have
selected have values in them
• SUM functions will add those values together (if they are numerical values)

In this example, we are going to provide a dynamic report for employee sales data. Our objective is
to generate a record that will tell us

1. How many sales and employee made? (COUNTIFS)

2. What is the total value of those sales? (SUMIFS)
• In our “Sales by Employee” worksheet, we have sales records (See Figure 1) for the first
week of June 2020.
• We then have the “Employee Sales Record” where we will insert the COUNTIFS function

Figure 1 Employee Sales Record

Figure 2 Employee Sales table

 1. Select cell H6
2. Begin to type in your COUNTIFS function
3. Note the list of COUNT functions appear

Figure 3 COUNT function options

4. Select COUNTIFS from the options and press the tab key to open the function

Figure 4 Select COUNTIFS

5. Note that Excel will describe the arguments required for this function (Figure 5)

Figure 5 Function arguments

6. All arguments are separated by a comma

7. Our first argument is the criteria range
8. The criteria range is the range of cells we want excel to count
Figure 6 Criteria Range

9. In this example, our criteria range is C6:C52

10. Once criteria range is selected, press comma

Figure 7 Comma to separate each argument

11. Our next argument is the criteria

12. We could insert the employee ID for the employee we want to produce a sales record for
13. To do this, we would need to use inverted commas this value is text or a string

Figure 8 Manually insert criteria

14. However, this is not good practice

15. We would need to change the criteria in our function for each employee
16. Remember the golden rule
17. Therefore, we will reference a cell instead
18. For the criteria argument reference cell F6
19. Close brackets and press enter

Figure 9 Applying the golden rule for criteria argument

20. Your result will be zero at first

21. Now enter in an employee ID – e.g. EMP104
 22. COUNTIFS Function will produce the total number of times this employee appears in the
sales table
23. i.e. Emp104 made six sales in the first week of June 2020

Figure 10 Working COUNTIFS Function

SUMIFS Function
• To calculate the total sum of sales for a specific employee we need to use the SUMIFS
function
• SUMIFS Function has one additional argument
• Along with criteria range and criteria, the SUMIFS function also requires the SUM range
• In our Employee Sales Record, we will now insert a SUMIFS function to calculate the total
sales for a specific employee

1. Select cell I6 and begin to type in your SUMIFS function

2. Note it appear as you begin to type in the function
3. Select SUMIFS and press the tab key

Figure 11 SUMIFS Function

4. Note in the arguments prompter we have an additional argument – sum_range

5. Select the sum range (Sales Column)

Figure 12 Selecting the SUM range

 6. Follow the same steps for the remainder of the arguments as was applied for the COUNTIFS
function

Figure 13 Working SUMIFS function

7. To test both functions enter a different employee ID into the EMPID cell
8. Note how the functions update automatically
• However, we can make this record more automated
• We do not have to type in an employee ID each time
• Instead we can use a feature in Excel known as data validation
• Data validation allows us to implement a scope or boundary to a cell or range of cells
• In our Employee Sales Record, we will now insert data validation in the form of a drop-
down list for Employee ID
DATA VALIDATION
To insert data validation for our example, do the following

1. Select the EmpID cell in the Employee Sales Record

2. Navigate to the DATA tab in the ribbon

Figure 14 Data Tab

3. Navigate to DATA TOOLS

4. Here you will see multiple icons
5. Hover the cursor over the tick/stop icons (See figure 14)
6. This is data validation

Figure 15 Data Validation option

7. Click to open the data validation window

8. Here you have the validation criteria, input message tab, and error alert tab (Figure 16)
Figure 16 Data Validation settings

9. In the settings tab, we want to define the scope/set the criteria for or DV
10. Click on the ALLOW drop down
11. Note all the various validation options (Figure 17)

Figure 17 DV Allow criteria options

12. Choose list

13. Here we want to perform data validation in the form of a drop-down list
Figure 18 Drop-down list settings

14. In the drop-down list settings (figure 18) make sure “ignore blank” and “in-cell dropdown”
are ticked
15. Next, select the source dialogue box
16. Here, we need to tell the system what data to generate the list from
17. We can manually input these, however, this is not a good idea (reference)
18. In our workbook we have an “Employee List” worksheet
19. Once you have selected the source box, click on the “Employee List” worksheet
20. In the “Employee List” sheet select/highlight all the employee IDs in the table ( Figure 19)

Figure 19 Select Emp_ID

21. Note: When selected, the source box in the data validation window will describe the range
and also the worksheet the data is coming from
 22. Because the Employee IDs are coming from a different worksheet excel will need to
reference the worksheet along with the range of cell references
23. See Figure 20

Figure 20 Source list from different worksheet

24. Press OK
25. We now have a drop-down list of employee IDs in our employee sales record (Cell F6)

Figure 21 Data Validation drop-down list