SUMPRODUCT and ISNUMBER(SEARCH) in an array? 1

[TomYC]

I wish to count occurrences of a word in one of two columns in a worksheet, within a date range. So I have:

=SUMPRODUCT(ISNUMBER(SEARCH("heparin",DATA!$J$2:$K$25))*((DATA!$B$2:$B$25>VALUE("12/31/2007"))*(DATA!$B$2:$B$25<=VALUE("3/31/2008"))))

Problem is I'm getting a double count of records where the search term is in both columns, i.e. I'm getting 12 as an answer instead of 10 because two items satisfy the ISNUMBER(SEARCH) function twice, once in each column.
Is there a way to "OR" this? I tried using each column separately, with a "+" sign, but it didn't work or I didn't write it correctly. Thanks!

 

[SkipVought]

Plese post a cogent example of your table, illustrating the issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Here is as simplified a version as I can muster; I've put column headings in as "B", "J", & "K" --
B J K
1/1/2008 xx heparin
1/1/2008 lovenox xx
1/4/2008 xx Heparin
1/8/2008 Adrenalin Heparin
1/14/2008 xx coumadin
1/18/2008 vancomycin xx
1/24/2008 dilaudid dilaudid
1/24/2008 vancomycin vancomycin
1/27/2008 dilaudid dilaudid
1/30/2008 dilaudid dilaudid
2/4/2008 Morphine Morphine
2/11/2008 heparin heparin
2/24/2008 dilaudid dilaudid
2/25/2008 novolog novolin R
3/6/2008 heparin heparin
3/7/2008 heparin xx
3/8/2008 heparin xx
3/10/2008 heparin xx
3/15/2008 dilaudid dilaudid
3/17/2008 heparin lovenox
3/18/2008 dilaudid dilaudid
3/27/2008 LantusInsulin Regular insulin
3/29/2008 xx heparin
3/30/2008 ativan/lovenox xx

I note that the text string search is necessary
because the cell may contain more than one word, etc.

 

[SkipVought]

how about this...
[tt]
=SUMPRODUCT(ISNUMBER(SEARCH("heparin",DATA!$J$2:$K$25))*((DATA!$B$2:$B$25>VALUE("12/31/2007"))*(DATA!$B$2:$B$25<=VALUE("3/31/2008"))))-SUMPRODUCT(ISNUMBER(SEARCH("heparin",DATA!$J$2:$J$25))*ISNUMBER(SEARCH("heparin",DATA!$K$2:$K$25))*((DATA!$B$2:$B$25>VALUE("12/31/2007"))*(DATA!$B$2:$B$25<=VALUE("3/31/2008"))))
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

That works and warrants a star!
I'm sure if there were a more elegant solution, besides subtracting the duplicates from an overcount, you'd have offered it. So I am glad to accept a pragmatic accuracy--Thanks

 

[Gruuuu]

What about adding a concatenate column for J and K?

=J2&"|"&K2

etc
And use your original formula on that column?