Microsoft Excel - COUNTIFS with Text String as Condition

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

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