Using =COUNTIF() Function to Validate Data

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:

1. Use the =COUNTIF() Function to count the number of values in a Range that match the Criteria that you set.
2. 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.
3. For the Criteria, point and click the cell in Column D that contains the label for our first department (Accounting) and Click OK.
4. You can now copy the formula down to the other cells in Column E – This gives you a “Head Count” by Department.
5. 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.
6. On the SELECT tab “Allow” drop-down, click “Custom.” Enter this formula in cell A2:
7. =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
8. 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

• Three ways to minimize data entry errors in Excel