Complicated Range Question

Burkholder

New Member
Joined
Feb 20, 2004
Messages
1
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
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top