How to Use SUMIFS Function in Data Validation

Analysts spend between 60% to 80% cleaning data. Here is how to cut that time by using the SUMIFS function in Data Validation.

Given the below data, how do you ensure staff is awarded only salaries within their job grade scale?

Here are the 4 steps:

1. Select the Salary Column and use short cut Alt + A + V +V to access the data validation
2. Select allow whole numbers and Data between
3. For minimum, write this SUMIFS function that looks up minimum salary per salary grade =SUMIFS(J5:J9, I5:I9, F5)
4. For maximum, write this SUMIFS function that looks up maximum salary per salary grade=SUMIFS(K5:K9, I5:I9, F5)

DOWNLOAD WORKSHEET

Watch the step-by-step video 👇