How Do I use COUNTIF Combining the Text from 2 Columns

K

Kevin

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
 
J

JE McGimpsey

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.
 
R

Ron Coderre

Perhaps something like this?:

For values in A1:B100

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

Does that help?

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

XL2002, WinXP-Pro
 
R

Ron Coderre

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
 
D

Dave Peterson

=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).
 
Top