Complicated Range Question

[Burkholder]

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

 

[Zack Barresse]

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