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.
- First, under the Data tab, select Data Validation from the Data Tools group.
- 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:
- 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.
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.
- 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:
- Afterward, press OK.
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.
- Excel VBA to Create Data Validation List from Array
- Use Data Validation List from Another Sheet (6 Methods)
- How to Use Data Validation in Excel with Color (4 Ways)
- Use IF Statement in Data Validation Formula in Excel (6 Ways)
- How to Create Excel Drop Down List for Data Validation (8 Ways)
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.
- 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.
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.
- 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:
- 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.
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.
- Excel Data Validation Drop Down List with Filter (2 Examples)
- How to Make a Data Validation List from Table in Excel (3 Methods)
- Data Validation Drop Down List with VBA in Excel (7 Applications)
- [Fixed] Data Validation Not Working for Copy Paste in Excel (with Solution)
- How to Use Named Range for Data Validation List with VBA in Excel