Microsoft Excel - COUNTIFS with Text String as Condition

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

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

-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?


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)


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.


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