Using Sumproduct to count text occurence

I'm counting excel records (rows) using the following conditions:

=SUMPRODUCT((Ship05!$A$2:$A$428<>"")*(Ship05!$B$2:$B$428>DATEVALUE("03/31/05"))*(Ship05!$B$2:$B$428<=DATEVALUE("06/30/05"))*(Ship05!$M$2:$M$428="Freight
Damage"))

Instead of "Freight Damage" in column M, I want use a wildcard to count the
records containing other "Freight" text strings.
--
Dave

You might be able to use

ISNUMBER(search("Freight",Ship05!$M$2:$M$428))

"Dave" wrote:

> I'm counting excel records (rows) using the following conditions:
>
> =SUMPRODUCT((Ship05!$A$2:$A$428<>"")*(Ship05!$B$2:$B$428>DATEVALUE("03/31/05"))*(Ship05!$B$2:$B$428<=DATEVALUE("06/30/05"))*(Ship05!$M$2:$M$428="Freight
> Damage"))
>
> Instead of "Freight Damage" in column M, I want use a wildcard to count the
> records containing other "Freight" text strings.
> --
> Dave

That worked! How would I express the negative of that; i.e., count records
that did not contain "Freight"?
--
Dave


"Barb Reinhardt" wrote:

> You might be able to use
>
> ISNUMBER(search("Freight",Ship05!$M$2:$M$428))
>
> "Dave" wrote:
>
> > I'm counting excel records (rows) using the following conditions:
> >
> > =SUMPRODUCT((Ship05!$A$2:$A$428<>"")*(Ship05!$B$2:$B$428>DATEVALUE("03/31/05"))*(Ship05!$B$2:$B$428<=DATEVALUE("06/30/05"))*(Ship05!$M$2:$M$428="Freight
> > Damage"))
> >
> > Instead of "Freight Damage" in column M, I want use a wildcard to count the
> > records containing other "Freight" text strings.
> > --
> > Dave