COUNTIF with partial criteria match

%3CLINGO-SUB%20id%3D%22lingo-sub-2028120%22%20slang%3D%22en-US%22%3ECOUNTIF%20with%20partial%20criteria%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2028120%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20made%20a%20spreadsheet%20for%20physical%20counting%20inventory%2C%20using%20a%20barcode%20scanner.%3C%2FP%3E%3CP%3EColumn%20A%20contains%20each%20item%20number%2C%20subsequent%20columns%20list%20item%20description%2C%20location%2C%20and%20cross%20reference%20part%20numbers.%3C%2FP%3E%3CP%3EI%20use%20COUNTIF%20to%20count%20the%20number%20of%20times%20an%20item%20%23%20is%20entered%20in%20the%20barcode%20scanner%20column%20to%20count%20inventory.%3C%2FP%3E%3CP%3EOK%2C%20so%20some%20of%20my%20items%20have%20barcodes%20with%20the%20cross%20reference%20numbers%20only%2C%20which%20are%20sometimes%20comma%20separated%20in%20the%20same%20cell%20in%20my%20spreadsheet%2C%20so%20count%20if%20does%20not%20match%20it.%3C%2FP%3E%3CP%3EI've%20tried%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(A1%3AA100%2C%22*%22%26amp%3BE1%26amp%3B%22*%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhich%20works%20exactly%20opposite%20of%20what%20I%20need.%3C%2FP%3E%3CP%3EI%20need%20the%20wildcards%20on%20range%2C%20not%20the%20criteria.%3C%2FP%3E%3CP%3Ei.e.%20E1%20contains%20two%20numbers%3A%205555%2C%204444.%3C%2FP%3E%3CP%3EI%20have%20two%20of%205555%2C%20So%20I%20scan%20them%20into%20both%20A1%20and%20A2.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20above%20formula%20works%20when%20my%20scanned%20number%20includes%20the%20complete%20text%20in%20E1%20plus%20some%2C%20like%20125555%2C%204444789%3B%20but%20I%20need%20it%20to%20work%20when%20say%20at%20least%205%20characters%20match.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%20or%20do%20I%20need%20to%20separate%20my%20part%20numbers%20to%20individual%20cells%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3EEDIT%3A%20I've%20attached%20the%20actual%20spreadsheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2028120%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hi,

I have made a spreadsheet for physical counting inventory, using a barcode scanner.

Column A contains each item number, subsequent columns list item description, location, and cross reference part numbers.

I use COUNTIF to count the number of times an item # is entered in the barcode scanner column to count inventory.

OK, so some of my items have barcodes with the cross reference numbers only, which are sometimes comma separated in the same cell in my spreadsheet, so count if does not match it.

I've tried

 

 

=COUNTIF(A1:A100,"*"&E1&"*")

 

 

which works exactly opposite of what I need.

I need the wildcards on range, not the criteria.

i.e. E1 contains two numbers: 5555, 4444.

I have two of 5555, So I scan them into both A1 and A2.

 

The above formula works when my scanned number includes the complete text in E1 plus some, like 125555, 4444789; but I need it to work when say at least 5 characters match.

 

Is this possible or do I need to separate my part numbers to individual cells?

Thanks

EDIT: I've attached the actual spreadsheet.

0 Replies