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:
- 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.
May 28th, 2011 at 3:04 AM
please tell me detail about data validation in excel in 2003
May 28th, 2011 at 5:52 AM
Thank you for contacting me. Here is a link to a video that i created using Excel 2003 demonstrating Data Validation:
http://www.thecompanyrocks.com/3-ways-to-minimize-data-entry-errors-in-excel/
Danny Rocks