Identifying correct elements with Countif

L

Lady Success

I’m trying to count the number of instances a specific entry appears and then
I want to display the instances it found in the database. My logic for the
count appears to be working ok – but it is not identifying the correct items.


In O2, my formula reads: =IF(Query!B3="","",COUNTIF(ChemName,DRUGENTRY))

In O4, my array formula reads:
{=IF(ROWS(O$4:O4)<=O$2,INDEX(DRUGLIST,SMALL(IF(ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),ROWS(O$4:O4))-MIN(ROW(ChemName))+1,2),"")}
I copied the formula down to the cells below.

One tab contains the data entry of: “epoetin alfaâ€

My database looks like the following:
Drug Name Chemical Name J code
Aranesp darbepoetin alfa J0882
Aranesp darbepoetin alfa J0881
Epogen epoetin alfa J0886
Epogen epoetin alfa J0885
Epogen epoetin alfa J2505
Neumega oprelvekin J2355
Neupogen filgrastim J1440

The result I get for the count is 3 (which is right). But the items
identified as the three it found are: darbepoetin alfa (twice) and epoetin
alfa (once). I do not want to include the darbepoetin alfa (even though it
includes the search name. I just want to capture the epoetin alfa. I only
want to include exact matches to the data entry. I’ve tried modifying my
formula in several different ways, but I still can’t get it to come back with
the exact match. If you could steer me in the right direction, I would
appreciate it.
 
T

T. Valko

I only want to include exact matches to the data entry.

Replace:
ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST))

With:
DRUGLIST=DRUGENTRY
 
L

Lady Success

That worked well for the epoetin alfa scenario, but now when I enter
filgrastim as my data entry, it gives me #N/A error.... If I'm looking for
an exact match, why doesn't it work when there's just one word and not two?
 
T

T. Valko

Could be unseen characters like spaces in either the DRUGENTRY or DRUGLIST
ranges:

filgrastim<space>
<space>filgrastim
<space>filgrastim<space>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top