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).