Let’s say you have a dataset containing information about a car fleet and you need to create a column with usage group based on number of KM each car was used.
I’ll use this file, sheet [2 Removing duplicates] in the following examples.
Step1: Calculate the sum per car
A simple sumif will give you the full distance.
Step2: Create groups
A. Using IF
Let’s say you need the following groups:
- =IF(F2<=200,"<=200 KM",IF(AND(F2>=201,F2<=300),"201-300 KM",IF(AND(F2>=301,F2<=400),"301-400 KM",IF(F2>400,">400 KM",0))))
B. Using VLOOKUP
Using IF might give you headaches so you could use VLOOKUP instead.
You need to create a lookup table like this:
Now, assuming you have the sum of KM in column F and the lookup table in columns I:J, you can use this formula:
Note that range_lookup argument is missing so it will be an approximate search, VLOOKUP returning the next lowest match to your search term.