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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn


  1. PARVEEN KUMAR says:

    please tell me detail about data validation in excel in 2003

  2. Thank you for contacting me. Here is a link to a video that i created using Excel 2003 demonstrating Data Validation:

    Danny Rocks

Speak Your Mind