Sumproduct to count

I have a table where I want to count the number of times "*VPN*" appear
in column A where column K is blank, something like:

=SUMPRODUCT(--($A$6:$A$1000="*VPN*"),--($K$6:$K$1000="")).

The result should be 14 but is returning 0. The countif formula

=COUNTIF($A$6:$A$1000,"*VPN*")=14 and the corresponding rows in
K6:K1000 = "".

What is wrong? TIA

Greg

=SUMPRODUCT(--(ISNUMBER(FIND("VPN",$A$6:$A$1000))),--($K$6:$K$1000=""))

If you want it to be non-case sensitive, use SEARCH not FIND.

--
HTH

Bob Phillips

"GregR" <[email protected]> wrote in message
news:[email protected]...
> I have a table where I want to count the number of times "*VPN*" appear
> in column A where column K is blank, something like:
>
> =SUMPRODUCT(--($A$6:$A$1000="*VPN*"),--($K$6:$K$1000="")).
>
> The result should be 14 but is returning 0. The countif formula
>
> =COUNTIF($A$6:$A$1000,"*VPN*")=14 and the corresponding rows in
> K6:K1000 = "".
>
> What is wrong? TIA
>
> Greg
>

I know that page :-)

Bob

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Since this is the second instance of you saveing the day on the sumproduct
> formula I thought it might be helpful to post a cheat sheet of how this
stuff
> is done...
>
> http://www.xldynamic.com/source/xld.SUMPRODUCT.html
>
> Example 9 in this case... I know because I had to look it up. In this case
> Greg was looking for the literal value *VPN* not containing VPN.
> --
> HTH...
>
> Jim Thomlinson
>
>
> "Bob Phillips" wrote:
>
> > =SUMPRODUCT(--(ISNUMBER(FIND("VPN",$A$6:$A$1000))),--($K$6:$K$1000=""))
> >
> > If you want it to be non-case sensitive, use SEARCH not FIND.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "GregR" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a table where I want to count the number of times "*VPN*"
appear
> > > in column A where column K is blank, something like:
> > >
> > > =SUMPRODUCT(--($A$6:$A$1000="*VPN*"),--($K$6:$K$1000="")).
> > >
> > > The result should be 14 but is returning 0. The countif formula
> > >
> > > =COUNTIF($A$6:$A$1000,"*VPN*")=14 and the corresponding rows in
> > > K6:K1000 = "".
> > >
> > > What is wrong? TIA
> > >
> > > Greg
> > >
> >
> >
> >

Bob and Jim, once again thank you for your support and astute answers.

Greg