# Microsoft Excel - COUNTIFS with Text String as Condition

Asked By C R on 22-Jan-13 11:51 PM
Hi,

How would i incorporate the below COUNTIF function into a COUNTIFS statement?

=COUNTIF(\$F\$2:\$F\$341,"*"&Lookups!\$B3&"*")
-This function works well, it counts the number of times "China" would appear in a text string.

=COUNTIFS('ITC Data'!\$F\$2:\$F\$341,"*"&Lookups!\$B3&"*",'ITC Data'!\$B\$2:\$B\$346,1)
- Doesn't return a value, b/c first portion of statement is text string
- How would i amend this statement to count if column F contains a word in the string and a 1 in column B?

Thanks,
CR

Donald Ross replied to C R on 23-Jan-13 12:31 AM
try this
=COUNTIFS('ITC data'!\$F\$2:\$F\$341,"*"&lookups!\$B3&"*")+COUNTIFS(\$B\$2:\$B\$341,1)

Don

looking at your countifs you have two ranges and only one criteria the reference to B3 on lookups and then you are looking for 1 in B2:B341 this syntax will work but may not generate the right answer if you are looking for just those cells that have (china your example and also have a 1 in the coresponding row.

1      Africa
1      China
1      Brazil

Will count 4 they way I did it.   if you need it to count just 1 then your will need to try it this way.

=COUNTIFS(\$F\$2:\$F\$341,lookups!\$B\$3,\$B\$2:\$B\$341,1)

I did this on my sheet and I did not name the sheet ITC data but you can add that back.

Don