Apply Custom Data Validation for Multiple Criteria in Excel (4 Examples)

Sometimes we need to input data based on multiple criteria in our Excel datasheet. Data entries that don’t follow our desired conditions may create problems in our worksheet. So we expect to have an error message if we input wrong entries. In this article, we’ll show you some examples for applying Custom Data Validation for Multiple Criteria in Excel.


Download Practice Workbook

Download the following workbook to practice by yourself.


4 Examples to Apply Custom Data Validation for Multiple Criteria in Excel

1. Apply Custom Data Validation for Multiple Criteria in One Excel Cell

The Excel OR function finds out if any of the arguments or conditions in the argument is true or not. We use this OR function when we have to deal with multiple criteria. To illustrate, I’m going to use a sample dataset as an example. For instance, the following dataset has two criteria: Criteria 1 contains a list of products, and Criteria 2 has 2 specific dates. Here, we’ll apply the OR function in cell B5 so that the input meets any of the criteria. Therefore, follow the below steps to apply Custom Data Validation for Multiple Criteria in Excel.

Apply Custom Data Validation for Multiple Criteria in One Excel Cell

STEPS:

  • First, under the Data tab, select Data Validation from the Data Tools group.

Apply Custom Data Validation for Multiple Criteria in One Excel Cell

  • As a result, the Data Validation dialog box will pop out.
  • Then, under the Settings tab, select Custom in the field: Allow.
  • After that, in the Formula box, type the formula:
=OR(COUNTIF($D$5:$D$10,B5)=1, AND(B5>=E5,B5<=E6))
  • Subsequently, press OK.

Here, the AND function checks whether the date input is between E5 (1-Feb-2022) & E6 (1-Mar-2022). The COUNTIF function checks for the B5 text value to be from the range D5:D10. And at last, the OR function checks if the B5 cell input satisfies any of the specified conditions.

  • Finally, you’ll be able to enter any of the products from Criteria 1 or any date between the specified dates.
  • But, if you enter anything which doesn’t follow either of the conditions, you’ll get an error dialog box.

Read More: How to Apply Multiple Data Validation in One Cell in Excel (3 Examples)


2. Use Custom Data Validation for Multiple Criteria in Selected Cells

Additionally, we can apply the data validation in a range of cells instead of just a single cell shown in the previous example. In this below dataset, we have 2 criteria: Criteria 1 has a list of products, and Criteria 2 has a number input which is 50. In this example, we’ll apply the Custom Data Validation for Multiple Criteria in the range B5:B10. So, learn the steps given below to input any data which follows either Criteria 1 or Criteria 2.

Use Custom Data Validation for Multiple Criteria in Selected Cells

STEPS:

  • Firstly, select the range B5:B10.
  • Next, go to Data Data Tools Data Validation. Consequently, the Data Validation dialog box will pop out.
  • There, under the Settings tab, select Custom in the Allow field, and in the Formula box, type:
=OR(B5<$E$5,COUNTIF($D$5:$D$10,B5)=1)
  • Afterward, press OK.

Use Custom Data Validation for Multiple Criteria in Selected Cells

The COUNTIF function checks whether the input is from the range D5:D10 and will count only if it is present in the range. The next criteria are to see if the input is Smaller than E5 (50). At last, the OR function checks if the inputs in the range B5:B10 satisfy any of the conditions.

  • Eventually, you’ll be able to input valid data following the conditions.
  • For this example, oven and 15 in cells B5 and B6 are valid. But, when we try to input 59, it returns an error message as 59 is Greater than 50.

Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel


Similar Readings:


3. Prevent Duplicate Entries in Excel and Limit the Number of Characters with Custom Data Validation

Moreover, we can prevent duplicate entries and also limit the number of characters by applying Custom Data Validation. For instance, the below dataset represents the ID, Salesman, and Product of a company. Here, we’ll complete the list of IDs with number entries of 3 digits and without repeating any values. Hence, learn the process given below to perform the task.

Prevent Duplicate Entries in Excel and Limit the Number of Characters with Custom Data Validation

STEPS:

  • In the beginning, select the range B5:B10.
  • Then, go to Data Data Tools Data Validation.
  • As a result, a dialog box will appear.
  • Under the Settings tab, select Custom in Allow. Next, in the Formula box, type the formula:
=AND(COUNTIF($B$5:$B$10,B5)<=1, ISNUMBER(B5), LEN(B5)=3)
  • Press OK.

In brief, the ISNUMBER function takes only number inputs. The LEN function checks for the input for only 3 digits. The COUNTIF function prevents any duplicate entries. And lastly, the AND function checks whether the entries satisfy all the conditions or not. It’ll be valid only if the entries satisfy all the conditions.

  • In the end, you can input any valid data maintaining all the criteria.
  • Otherwise, it’ll result in an error dialog box.

Read More: How to Use Custom VLOOKUP Formula in Excel Data Validation


4. Allow Dates between Two Dates by Using Custom Data Validation for Multiple Criteria in Excel

In our last example, we’ll show how to allow date entries between two given dates. In the following dataset, we have a start date in cell D12 and an end date in cell D13. We have to input the Dispatch Date of each Salesman between those two dates. Therefore, follow the below process.

Allow Dates between Two Dates by Using Custom Data Validation for Multiple Criteria in Excel

STEPS:

  • Select the range D5:D10 at first.
  • Now, select Data Data Tools Data Validation.
  • The Data Validation dialog box will pop out.
  • Under the Settings tab, select Custom in the Allow In the Formula box, type:
=AND(D5>=$D$12, D5<=$D$13)
  • Then, press OK.

Here, the AND function checks whether the date entries fall between the dates mentioned in cells D12 and D13.

  • Hence, you’ll be able to input dates following the criterion. But as soon as you type any invalid date which doesn’t satisfy the condition, you’ll get an error message.
  • In this example, although 29-02-2022 falls between the specified dates, it shows an error box. This is because 2022 is not a leap year and thus, 29th February doesn’t exist.

Related Content: Default Value in Data Validation List with Excel VBA (Macro and UserForm)


Conclusion

Henceforth, you will be able to apply Custom Data Validation for Multiple Criteria in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo