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" wrote in message
ups.com...
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

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" wrote in message
ups.com...
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" wrote in message
...
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" wrote in message
ups.com...
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