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
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn

Comments

  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:
    http://www.thecompanyrocks.com/3-ways-to-minimize-data-entry-errors-in-excel/

    Danny Rocks

Speak Your Mind

*