How Do I use COUNTIF Combining the Text from 2 Columns
 

I want to do a COUNTIF using the text combine in 2 columns.
I tried using concatenate =COUNTIF(A:A&B:B,"FPP*Conn*") but I get an
formula error

How Do I use COUNTIF Combining the Text from 2 Columns
 

Not surprising it's a formula error - the first argument of COUNTIF() is
a range, not an array of text strings...

One way:

=SUMPRODUCT(--(LEFT(A1:A1000,3)="FPP"),--(LEFT(B1:B1000,4)="Conn")

For an explanation of "--" see

http://www.mcgimpsey.com/excel/doubleneg.html



In article .com,
"Kevin" wrote:

I want to do a COUNTIF using the text combine in 2 columns.
I tried using concatenate =COUNTIF(A:A&B:B,"FPP*Conn*") but I get an
formula error

How Do I use COUNTIF Combining the Text from 2 Columns
 

I get zero when i try that, any other suggestions?

How Do I use COUNTIF Combining the Text from 2 Columns
 

Do you have any rows for which the value in column A starts with "FPP"
and the value in the corresponding row in column B starts with "Conn"?
(Or did I misinterpret your requirements from your examples?)

It works for me in a test workbook.

In article .com,
"Kevin" wrote:

I get zero when i try that, any other suggestions?

How Do I use COUNTIF Combining the Text from 2 Columns
 

Perhaps something like this?:

For values in A1:B100

B1: =SUMPRODUCT((LEFT(A1:A100,3)="FPP")*(ISNUMBER(SEAR CH("CONN",B1:B100))))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Kevin" wrote:

I want to do a COUNTIF using the text combine in 2 columns.
I tried using concatenate =COUNTIF(A:A&B:B,"FPP*Conn*") but I get an
formula error

How Do I use COUNTIF Combining the Text from 2 Columns
 

Of course the formula couldn't be in B1...Anyplace other than Col_A or Col_B
would work, though.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Ron Coderre" wrote:

Perhaps something like this?:

For values in A1:B100

B1: =SUMPRODUCT((LEFT(A1:A100,3)="FPP")*(ISNUMBER(SEAR CH("CONN",B1:B100))))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Kevin" wrote:

I want to do a COUNTIF using the text combine in 2 columns.
I tried using concatenate =COUNTIF(A:A&B:B,"FPP*Conn*") but I get an
formula error

How Do I use COUNTIF Combining the Text from 2 Columns
 

Col B does not start with "Conn", it contains "Conn" as part of the
string of text

How Do I use COUNTIF Combining the Text from 2 Columns
 

=SUMPRODUCT(--(ISNUMBER(SEARCH("fpp",A1:A1000))),
--(ISNUMBER(SEARCH("conn",B1:B1000))))

(all one cell).

=search() is not case sensitive (FPP, Fpp, fpp will be counted).



Kevin wrote:

Col B does not start with "Conn", it contains "Conn" as part of the
string of text

--

Dave Peterson