|
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 to count
=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 |
Sumproduct to count
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 |
Sumproduct to count
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 |
Sumproduct to count
Bob and Jim, once again thank you for your support and astute answers.
Greg |
| All times are GMT +1. The time now is 01:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com