I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

Take a look at the Autofilter......you can select "warranty" in the one
column, and select "custom, contains switch" in the other..........if you
then need to count the filtered rows, you could use the SUBTOTAL formula.

hth
Vaya con Dios,
Chuck, CABGx3





"Rog" wrote:

I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

One way:

=SUMPRODUCT(--(X1:X1000="warranty"),
--(ISNUMBER(SEARCH("switch",AD1:AD1000))))


In article ,
Rog wrote:

I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

Rog wrote...
I need to use two columns and search for two things and count
how often the two occur together. In col X will occur the word
"warranty" and in column AD will occur, for example the word
"switch". The problem is that the word switch will occur in a
paragraph, so I need a wild card and SUMPRODUCT does not support
this. . . .

FWIW, only SUMIF, COUNTIF, SEARCH, MATCH and {V|H}LOOKUP support
wildcards, and the last 3 only for exact matching. However, if you're
looking for a particular word that would be separated from other text
by spaces, you don't need wildcards.

=SUMPRODUCT(COUNTIF(Range,{"test *","* test *","* test"}))

and

=SUMPRODUCT(--ISNUMBER(SEARCH(" test ",Range)))

return the same result. The array argument to COUNTIF in the first
formula is necessary to capture "test" appearing at the start or end
of each cell value in Range as well as appearing in the middle of the
string. Eliminating the spaces would mean you could match "test" as a
substring of other words, e.g., "detested". So for more rigorous
matching, SUMPRODUCT/ISNUMBER/SEARCH is actually simpler to use. And
as an added bonus, SEARCH allows you to use wildcards if you have to.

If you have 40000 records to search then I doubt SUMPRODUCT is the right
tool but you can use it to find strings that are part of other strings, de
facto wildcard

=SUMPRODUCT(--(ISNUMBER(SEARCH("warranty",A2:A40000))),--(AD2:AD40000="Switch"))



--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column
AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!

Thanks, but this value needs to go into a report and this approach is
cumbersome to do that. I also will need to do this for many other
combinations and that would take some time.

Is there anything else out there? This can't be THAT hard for MS!!

Thanks very much, though!

"CLR" wrote:

Take a look at the Autofilter......you can select "warranty" in the one
column, and select "custom, contains switch" in the other..........if you
then need to count the filtered rows, you could use the SUBTOTAL formula.

hth
Vaya con Dios,
Chuck, CABGx3





"Rog" wrote:

I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT does
not support this. I looked at the solutions provided for this, but none will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!! Needed
asap by the way. I have 40,000 records to search!

The easiest way would be to use a filter and VBA, it is much more cumbersome
to use formulas on 40000 rows of data.



--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
Thanks, but this value needs to go into a report and this approach is
cumbersome to do that. I also will need to do this for many other
combinations and that would take some time.

Is there anything else out there? This can't be THAT hard for MS!!

Thanks very much, though!

"CLR" wrote:

Take a look at the Autofilter......you can select "warranty" in the one
column, and select "custom, contains switch" in the other..........if you
then need to count the filtered rows, you could use the SUBTOTAL formula.

hth
Vaya con Dios,
Chuck, CABGx3





"Rog" wrote:

I need to use two columns and search for two things and count how often
the
two occur together. In col X will occur the word "warranty" and in
column AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set
of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!

I think you're missing the point here. I need to count the rows that have
both "warranty" and "switch".
col X col AD
warranty the switch broke
customer light
warranty the valve is bad
customer the switch failedh
warranty it was the switch that broke

The result desired here is 2 because two rows have "warranty" AND the word
"switch". Hope that clarifies it and thanks again!







"Peo Sjoblom" wrote:

If you have 40000 records to search then I doubt SUMPRODUCT is the right
tool but you can use it to find strings that are part of other strings, de
facto wildcard

=SUMPRODUCT(--(ISNUMBER(SEARCH("warranty",A2:A40000))),--(AD2:AD40000="Switch"))



--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column
AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

Rog wrote:
I think you're missing the point here. I need to count the rows that have
both "warranty" and "switch".
col X col AD
warranty the switch broke
customer light
warranty the valve is bad
customer the switch failedh
warranty it was the switch that broke

The result desired here is 2 because two rows have "warranty" AND the word
"switch". Hope that clarifies it and thanks again!
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook, you might
consider something like the following:

Assuming your lookup values are in X1:X5, Array enter into AE1:AE5

=SEARCH("switch",VLOOKUPs("warranty",X1:AD5,7),1) and enter into AF1

=COUNTIF(AE1:AE5,"<#VALUE!")

The result should be in AF1

Alan Beban

You can use DCOUNT by setting the criteria text to *switch*.

"Rog" wrote in message
...
I need to use two columns and search for two things and count how often the
two occur together. In col X will occur the word "warranty" and in column
AD
will occur, for example the word "switch". The problem is that the word
switch will occur in a paragraph, so I need a wild card and SUMPRODUCT
does
not support this. I looked at the solutions provided for this, but none
will
find the word "switch" in a paragraph. Is there another function or set of
functions that will do this? DCOUNT will not work because it's criteria
cannot be set to words with a wildcard character. Thanks in advance!!
Needed
asap by the way. I have 40,000 records to search!

Alan Beban <unavailable wrote...
....
If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook,
you might consider something like the following:

Assuming your lookup values are in X1:X5, Array enter into AE1:AE5
=SEARCH("switch",VLOOKUPs("warranty",X1:AD5,7), 1)
and enter into AF1
=COUNTIF(AE1:AE5,"<#VALUE!")

The result should be in AF1

The OP did mention that his data spans nearly 40K rows. The good news
is that there'd be only one udf VLOOKUPS call, and since its result
would presumably have far fewer than 40K entries, there'd effectively
be fewer SEARCH calls.

But why bother with entering an array formula in AE1:AE#? The SEARCH
will return an array of numbers or error values. All that'd be needed
is the SINGLE array formula

=COUNT(SEARCH("switch",VLOOKUPS("warranty",X1:AD#, 7)))

More efficient array formulas could be used that don't require udfs.

=COUNT(IF(X1:X#="warranty",SEARCH("switch",AD1:AD# )))

This will do less work than the VLOOKUPS formula much more quickly
since it avoids the Excel/VBA interface.

Note: replace # with the actual ranges' bottom row number.

WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base? If
I add the word "switch" to one of the records it will not update to show the
count plus one. Please advise. What is happening here? I have put the word at
the beginning of the record and inthe middle of it. I am using "*" before and
after the word. Is it because I have 40k records to update?

Thanks

"Rog" wrote:

WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

Do you have your calculation set to automatic under
toolsoptionscalculation?
What is the formula you are using?
Be aware that any array formula will be slow calculating 40000 rows


--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base?
If
I add the word "switch" to one of the records it will not update to show
the
count plus one. Please advise. What is happening here? I have put the word
at
the beginning of the record and inthe middle of it. I am using "*" before
and
after the word. Is it because I have 40k records to update?

Thanks

"Rog" wrote:

WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

Here is the formula. I do have the auto calc set. I tried going manual and
using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))

Thanks.

"Peo Sjoblom" wrote:

Do you have your calculation set to automatic under
toolsoptionscalculation?
What is the formula you are using?
Be aware that any array formula will be slow calculating 40000 rows


--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base?
If
I add the word "switch" to one of the records it will not update to show
the
count plus one. Please advise. What is happening here? I have put the word
at
the beginning of the record and inthe middle of it. I am using "*" before
and
after the word. Is it because I have 40k records to update?

Thanks

"Rog" wrote:

WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

Correction: "warranty does not have the "*". BTW, "BELLOW*" is supposed to
pick up all forms of "BELLLOWS" (Sometimes they spell it wrong) but count it
only once in any record.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))


"Rog" wrote:


Here is the formula. I do have the auto calc set. I tried going manual and
using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))

Thanks.

"Peo Sjoblom" wrote:

Do you have your calculation set to automatic under
toolsoptionscalculation?
What is the formula you are using?
Be aware that any array formula will be slow calculating 40000 rows


--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data base?
If
I add the word "switch" to one of the records it will not update to show
the
count plus one. Please advise. What is happening here? I have put the word
at
the beginning of the record and inthe middle of it. I am using "*" before
and
after the word. Is it because I have 40k records to update?

Thanks

"Rog" wrote:

WOW! Thanks to you all!!! I have not completely tested it, but so far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string) less
efficiently (sometimes only one level of function calls is necessary).

"Rog" wrote...
Here is the formula. I do have the auto calc set. I tried going manual
and using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),
--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))
....

No, you can't use wildcards in simple equality tests - "warranty*" would
only match substrings containing "warranty" immediately followed by an
asterisk. If you want to match "warranty" at the beginning of the col X
cells, only check the first 8 chars of each of those cells. And the SEARCH
string "BELLOW*" could be replaced with "BELLOW" because there the * is
superfluous.

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

try this idea
=SUMPRODUCT((LEFT(W2:W22,4)="bell")*(X2:X22))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rog" wrote in message
...
Correction: "warranty does not have the "*". BTW, "BELLOW*" is supposed
to
pick up all forms of "BELLLOWS" (Sometimes they spell it wrong) but count
it
only once in any record.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))


"Rog" wrote:


Here is the formula. I do have the auto calc set. I tried going manual
and
using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))

Thanks.

"Peo Sjoblom" wrote:

Do you have your calculation set to automatic under
toolsoptionscalculation?
What is the formula you are using?
Be aware that any array formula will be slow calculating 40000 rows


--
Regards,

Peo Sjoblom



"Rog" wrote in message
...
WAIT! THERE'S A PROBLEM! Why will it not "see" additions to the data
base?
If
I add the word "switch" to one of the records it will not update to
show
the
count plus one. Please advise. What is happening here? I have put the
word
at
the beginning of the record and inthe middle of it. I am using "*"
before
and
after the word. Is it because I have 40k records to update?

Thanks

"Rog" wrote:

WOW! Thanks to you all!!! I have not completely tested it, but so
far it
seems to work beautifully!
You were right... there was no misunderstanding!
Thank you so much!!
Roger

"Harlan Grove" wrote:

Rog wrote...
I think you're missing the point here.
....

No, Peo only got the string order wrong. Change his formula to

=SUMPRODUCT(--(X2:X40000="warranty"),
--ISNUMBER(SEARCH("switch",AD2:AD40000)))

and it will produce the result you claim to be seeking. The
ISNUMBER(SEARCH(..)) idiom is the STANDARD approach to indicating
whether a substring exists in a longer string, though, FTHOI, this
could also be done with (SUBSTITUTE(string,substring,"")=string)
less
efficiently (sometimes only one level of function calls is
necessary).

"Rog" wrote...
Correction: "warranty does not have the "*". BTW, "BELLOW*" is supposed
to pick up all forms of "BELLLOWS" (Sometimes they spell it wrong) but
count it only once in any record.
....

Is it BELLLOW, BELLOW or BELOW? Doesn't really matter. The * is superfluous.
SEARCH("xyz*",Range) and SEARCH("xyz",Range) always return the same result.
Wildcards in SEARCH are only useful between literal text, e.g.,

SEARCH("a*z",Range)

which could match the alphabet, "Anzania", "a long time ago in Zimbabwe",
but not "Zounds! Another 'a'!".

Anyway, there's no hope for matching misspelled words unless you use
approximate patterns that could match a lot of other text or unless you test
all the allowed misspellings, e.g., test BELOW, BEELOW, BELLOW, BELLLOW,
etc. Approximate text matching requires VBA/udfs.

Okay, but I still don't understand why if I add the word "BELLOW" to one of
the records and it goes through its 3 minutes of updating, it does not change
the number of "BELLOW" it sees. Any thoughts there?


"Harlan Grove" wrote:

"Rog" wrote...
Here is the formula. I do have the auto calc set. I tried going manual
and using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),
--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))
....

No, you can't use wildcards in simple equality tests - "warranty*" would
only match substrings containing "warranty" immediately followed by an
asterisk. If you want to match "warranty" at the beginning of the col X
cells, only check the first 8 chars of each of those cells. And the SEARCH
string "BELLOW*" could be replaced with "BELLOW" because there the * is
superfluous.

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

"Rog" wrote...
Okay, but I still don't understand why if I add the word "BELLOW" to one
of the records and it goes through its 3 minutes of updating, it does not
change the number of "BELLOW" it sees. Any thoughts there?

You are adding BELLOW in col AD in records in which col X begins with
warranty? You are using the formula

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

?

If so, the result should change.

"Rog" wrote:


Here is the formula. I do have the auto calc set. I tried going manual and
using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Harlan Grove" wrote in message
...
"Rog" wrote...
Okay, but I still don't understand why if I add the word "BELLOW" to one
of the records and it goes through its 3 minutes of updating, it does not
change the number of "BELLOW" it sees. Any thoughts there?

You are adding BELLOW in col AD in records in which col X begins with
warranty? You are using the formula

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

?

If so, the result should change.

I made up a 10 record version of the DB and tried it there and it worked just
fine. Apparently there is a glitch in EXCEL because it seems to be related to
the numbr of records in the DB. This is a real concern; I don't know of any
way around this one. Thank you so much for all the help you have given.

"Harlan Grove" wrote:

"Rog" wrote...
Okay, but I still don't understand why if I add the word "BELLOW" to one
of the records and it goes through its 3 minutes of updating, it does not
change the number of "BELLOW" it sees. Any thoughts there?

You are adding BELLOW in col AD in records in which col X begins with
warranty? You are using the formula

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

?

If so, the result should change.

"Don Guillett" wrote...
"Rog" wrote:
Here is the formula. I do have the auto calc set. I tried going manual
and using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),
--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))
....
"Harlan Grove" wrote in message
....
You are adding BELLOW in col AD in records in which col X begins with
warranty? You are using the formula

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

?
....

Either you sent before adding text or you meant that the OP already stated
the problem. If the latter, here are the respective formulas w/o quoting.

Rog:
=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),
--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))

me:
=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

Note the differences in the first SUMPRODUCT arg. The differences in the
second SUMPRODUCT are are irrelevant.

"Rog" wrote...
I made up a 10 record version of the DB and tried it there and it worked
just fine. Apparently there is a glitch in EXCEL because it seems to be
related to the numbr of records in the DB. This is a real concern;
I don't know of any way around this one. . . .

You could use a udf (user-defined function, written in VBA). It could be
more reliable processing huge ranges.


Function adhoc(r1 As Range, p1 As String, r2 As Range, p2 As String) As Long
Dim k As Long, n As Long

n = r1.Rows.Count

If n < r2.Rows.Count Or r1.Columns.Count 1 _
Or r2.Columns.Count 1 Then
adhoc = -1 'error: return -1: ranges aren't 1D/same size
Exit Function
End If

For k = 1 To n
If CStr(r1.Cells(k, 1).Value) Like p1 _
And CStr(r2.Cells(k, 1).Value) Like p2 Then adhoc = adhoc + 1
Next k

End Function


Use it in formulas like

=adhoc(Portfolio_Review!$X$2:$X$45001,"warranty*",
Portfolio_Review!$AD$2:$AD$45001,"*BELLOW*")

Note that this udf does accept wildcards, and it even accepts character
classes. Read about VBA's Like operator in VBA online help. This will NOT
speed up recalc, but it may be more reliable.

EXACTLY. I just tested it again and it will not update. It takes a good 3
minutes to tell me that, but it doesn't.

"Harlan Grove" wrote:

"Don Guillett" wrote...
"Rog" wrote:
Here is the formula. I do have the auto calc set. I tried going manual
and using F9, but that didn't change it either.

=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),
--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))
....
"Harlan Grove" wrote in message
....
You are adding BELLOW in col AD in records in which col X begins with
warranty? You are using the formula

=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

?
....

Either you sent before adding text or you meant that the OP already stated
the problem. If the latter, here are the respective formulas w/o quoting.

Rog:
=SUMPRODUCT(--(Portfolio_Review!$X$2:$X$45001="warranty*"),
--ISNUMBER(SEARCH("BELLOW*",Portfolio_Review!$AD$2:$ AD$45001)))

me:
=SUMPRODUCT(--(LEFT(Portfolio_Review!$X$2:$X$45001,8)="warranty" ),
--ISNUMBER(SEARCH("BELLOW",Portfolio_Review!$AD$2:$A D$45001)))

Note the differences in the first SUMPRODUCT arg. The differences in the
second SUMPRODUCT are are irrelevant.