|
Identifying correct elements with Countif
Im 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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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. Ive tried modifying my formula in several different ways, but I still cant get it to come back with the exact match. If you could steer me in the right direction, I would appreciate it. -- Lady |
Identifying correct elements with Countif
I only want to include exact matches to the data entry.
Replace: ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST)) With: DRUGLIST=DRUGENTRY -- Biff Microsoft Excel MVP "Lady Success" wrote in message ... 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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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. -- Lady |
Identifying correct elements with Countif
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? -- Lady "T. Valko" wrote: I only want to include exact matches to the data entry. Replace: ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST)) With: DRUGLIST=DRUGENTRY -- Biff Microsoft Excel MVP "Lady Success" wrote in message ... 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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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. -- Lady |
Identifying correct elements with Countif
Could be unseen characters like spaces in either the DRUGENTRY or DRUGLIST
ranges: filgrastim<space <spacefilgrastim <spacefilgrastim<space -- Biff Microsoft Excel MVP "Lady Success" wrote in message ... 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? -- Lady "T. Valko" wrote: I only want to include exact matches to the data entry. Replace: ISNUMBER(SEARCH(DRUGENTRY,DRUGLIST)) With: DRUGLIST=DRUGENTRY -- Biff Microsoft Excel MVP "Lady Success" wrote in message ... 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(ISN UMBER(SEARCH(DRUGENTRY,DRUGLIST)),ROW(ChemName)),R OWS(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 a 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. -- Lady |
| All times are GMT +1. The time now is 05:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com