It is great to be back posting on The Company Rocks Excels. Thank you for your patience as I have worked to complete a series of Excel 2007 Training Videos to be published this Spring.
Here are two ways to use the =COUNTIF() Function. First to count the number of employees in each department. And then, as a Formula in Data Valadation to ensure that employee numbers entered in a column are unique values.
Here are the steps to follow in this lesson:
- Use the =COUNTIF() Function to count the number of values in a Range that match the Criteria that you set.
- We have already named the Range $A$2: $B$27 as “Department.” Let’s use the F3 (Paste Names) Shortcut to select this for our Range Argument.
- For the Criteria, point and click the cell in Column D that contains the label for our first department (Accounting) and Click OK.
- You can now copy the formula down to the other cells in Column E – This gives you a “Head Count” by Department.
- For our second example, we are going to use DATA – VALIDATION to ensure that only “unique values” will be accepted in Column A – The “Employee Number” Column.
- On the SELECT tab “Allow” drop-down, click “Custom.” Enter this formula in cell A2:
- =COUNTIF($A$2:$A$27, A2)=1 This ensures that only unique values will be accepted. Note the use of “Absolute” and “Relative” references in the formula. The “Range” is stated as an “Absolute” $A2:$A$27
- You will need to copy the Data Validation from cell A2 down to this rest of the cells in the range that you wish to validate. In this video I use Paste Special – Validation.
Find the Excel Training Video that you want in the Index of Excel Topics
News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.
Related Videos
Link to this post!