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