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.

- =SUMIF(A:A,A2,D:D)

### Step2: Create groups

#### A. Using IF

Let’s say you need the following groups:

<=200 KM

201-300 KM

301-400 KM

>400 KM

#### B. Using VLOOKUP

Using IF might give you headaches so you could use VLOOKUP instead.

You need to create a lookup table like this:

0 | <=200 KM |

200 | 201-300 KM |

300 | 301-400 KM |

400 | >400 KM |

Now, assuming you have the sum of KM in column F and the lookup table in columns I:J, you can use this formula:

- =VLOOKUP(F2,I:J,2)

Note that **range_lookup** argument is missing so it will be an approximate search, VLOOKUP returning the next lowest match to your search term.