Finding count of item between number ranges between two tables

%3CLINGO-SUB%20id%3D%22lingo-sub-2807119%22%20slang%3D%22en-US%22%3EFinding%20count%20of%20item%20between%20number%20ranges%20between%20two%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807119%22%20slang%3D%22en-US%22%3E%3CP%3EHi.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20tables%2C%20one%20has%20unique%20rows%20and%20the%20other%20has%20multiple%20rows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20unique%20Rows%20table%26nbsp%3B%20has%20a%20SCAC%20column%2C%20four%20columns%20of%20details%20(AAR%20Mech%2C%20Length%20interior%2C%20Length%20exterior%2C%20and%20Doors)%2C%20Range%20from%2C%20Range%20to%2C%20Cu%20Ft%2C%20and%20count.%26nbsp%3B%20%26nbsp%3BThis%20table%20is%20updated%20as%20I%20download%20traffic%20reports%20that%20have%20been%20scanned%20and%20uploaded%20to%20a%20website.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20multiple%20rows%20table%20is%20in%20a%20different%20tab%20and%20has%20an%20equipment%20roster%20containing%20the%20SCAC%2C%20Car%20%23%20and%20additional%20information%20such%20as%20manufacturer%2C%20car%20type%2C%20cu%20ft%2C%20color%2C%20Brand%2C%20and%20Model%20%23.%26nbsp%3B%20%26nbsp%3BEach%20row%20is%20unique%20in%20the%20car%20number%20but%20most%20of%20the%20other%20columns%20have%20the%20same%20information%20between%20rows%20such%20as%20Manufacturer%20and%20car%20type.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20am%20trying%20to%20do%20is%20get%20a%20count%20of%20cars%20that%20are%20in%20the%20equipment%20roster%20having%20a%20%23%20that%20falls%20within%20the%20number%20range%20from%20and%20range%20to%20for%20each%20unique%20row.%26nbsp%3B%20%26nbsp%3BI%20want%20to%20know%20for%20each%20unique%20row%2C%20example%20BCIT%20with%20number%20range%20from%20841100%20to%20841599%2C%20how%20many%20cars%20in%20the%20equipment%20roster%20table%20fall%20within%20841100%20to%20841599.%26nbsp%3B%20%26nbsp%3B%20BCIT%20841100%20to%20841599%20shows%20up%2012%20times%20in%20the%20traffic%20reports%20so%20if%20there%20are%20none%20in%20the%20equipment%20roster%20I%20know%20I%20need%20to%20look%20for%20those%20cars.%26nbsp%3B%20If%20for%20example%2C%20CFPX%20range%204627%20to%204686%20shows%203%20times%20in%20the%20traffic%20report%20and%20I%20have%20two%20cars%2C%204627%20and%204639%20that%20I%20do%20not%20need%20to%20purchase%20any%20more%20CFPX%20cars.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20went%20through%20multiple%20searches%20and%20results%20were%20lookup%2C%20countifs%2C%20vlookups%2C%20and%20indexes%20and%20could%20not%20find%20any%20that%20would%20do%20what%20I%20am%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20the%20information%20I%20have%20given%20is%20straight%20forward%20but%20if%20I%20need%20to%20attach%20example%20files%20let%20me%20know.%26nbsp%3B%20thanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMichael%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2807119%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2807428%22%20slang%3D%22en-US%22%3ERe%3A%20Finding%20count%20of%20item%20between%20number%20ranges%20between%20two%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807428%22%20slang%3D%22en-US%22%3E%3CP%3E%40t%20would%20have%20been%20helpful%20if%20you%20had%20a%20sample%20file%20with%20perhaps%20a%20dozen%20rows%20and%20the%20desired%20results%20from%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20my%20understanding%20of%20the%20question%2C%20you%20are%20probably%20wanting%20a%20COUNTIFS%20formula%20that%20matches%20the%20SCAC%20and%20counts%20the%20number%20of%20rows%20where%20the%20car%20%23%20is%20within%20the%20from%20and%20to%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20test%20workbook%2C%20I%20named%20the%20worksheets%20UniqueRows%20and%20EquipmentList.%20UniqueRows%20column%20A%20is%20SCAC%2C%20column%20F%20is%20the%20From%20and%20column%20G%20is%20the%20To.%20EquipmentList%20column%20A%20is%20SCAC%20and%20column%20B%20is%20the%20car%20%23.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20put%20the%20following%20formula%20in%20UniqueRows%20cell%20I2%20and%20copied%20it%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIFS(EquipmentList!A%3AA%2CA2%2CEquipmentList!B%3AB%2C%22%26gt%3B%3D%22%20%26amp%3B%20F2%2CEquipmentList!B%3AB%2C%22%26lt%3B%3D%22%20%26amp%3B%20G2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi. 

 

I have two tables, one has unique rows and the other has multiple rows.

 

The unique Rows table  has a SCAC column, four columns of details (AAR Mech, Length interior, Length exterior, and Doors), Range from, Range to, Cu Ft, and count.   This table is updated as I download traffic reports that have been scanned and uploaded to a website.  

 

The multiple rows table is in a different tab and has an equipment roster containing the SCAC, Car # and additional information such as manufacturer, car type, cu ft, color, Brand, and Model #.   Each row is unique in the car number but most of the other columns have the same information between rows such as Manufacturer and car type. 

 

What I am trying to do is get a count of cars that are in the equipment roster having a # that falls within the number range from and range to for each unique row.   I want to know for each unique row, example BCIT with number range from 841100 to 841599, how many cars in the equipment roster table fall within 841100 to 841599.    BCIT 841100 to 841599 shows up 12 times in the traffic reports so if there are none in the equipment roster I know I need to look for those cars.  If for example, CFPX range 4627 to 4686 shows 3 times in the traffic report and I have two cars, 4627 and 4639 that I do not need to purchase any more CFPX cars. 

 

I went through multiple searches and results were lookup, countifs, vlookups, and indexes and could not find any that would do what I am trying to do.

 

I think the information I have given is straight forward but if I need to attach example files let me know.  thanks

 

Michael

 

2 Replies

@t would have been helpful if you had a sample file with perhaps a dozen rows and the desired results from the formula.

 

Based on my understanding of the question, you are probably wanting a COUNTIFS formula that matches the SCAC and counts the number of rows where the car # is within the from and to range.

 

In my test workbook, I named the worksheets UniqueRows and EquipmentList. UniqueRows column A is SCAC, column F is the From and column G is the To. EquipmentList column A is SCAC and column B is the car #.

 

I put the following formula in UniqueRows cell I2 and copied it down.

 

=COUNTIFS(EquipmentList!A:A,A2,EquipmentList!B:B,">=" & F2,EquipmentList!B:B,"<=" & G2)

 

@byundt That worked perfect.  Thank you.  I am getting too old for Excel..lol.