# Complicated Range Question

#### Burkholder

##### New Member
Greetings,
I work for a business that does contract work for a shipping company called airborne express. Each week we are sent excel files in which the fails for each route is listed. We have several routes and doing this for each day of the week can become quite time consuming. There are the totals for all routes for each type of fail. Then under the totals each type of fail is broken down into the number of fails for each route.

Such as:
Fail 1
716
716
716
720

So for this type of fail there would be 3 fails for route 716 and 1 fail for route 720. I can do a countif to find the amount for 716 and the amount for 720 but that is only for one day. I would have to go through and change the range for every day which would be more time consuming that the original effort. My idea was to go to the top where to totals is and say there are 4 fails for type Fail 1 well you would start at the cell which the first route is in and then end at that cell + 3 which would give you 4 fails. My problem is that when I add the 3 to the cell it takes the number in that cell (in this case 716) and adds 3 to it making it 719. If anyone knows what I could do or has any other ideas let me know. Since the number of fails for each type of fails changes everyday the range must change accordingly.

Thanks,
John Burkholder

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### Zack Barresse

##### MrExcel MVP
how about a countif referencing a cell with the number in it, like...

=countif(range_to_count,"="&cell_reference)

you could change the cell reference, even create a drop down list to choose from if you'd like.

HTH

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,811
Messages
5,855,778
Members
431,763
Latest member
AlexisChch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back