How to count ocurrences of a given text string if another column in the same row presents a given value [duplicate]

I have a large excel sheet with the following structure:

``````Gender  | Answer
------- | -------
Male    | A
Female  | B
Male    | A;C
Female  | A;B
Female  | B;D
``````

What formula can I use to find out how many women answered, exclusively or not, B?

-

marked as duplicate by CharlieRB, Mokubai♦, Windos, Journeyman Geek♦, Canadian LukeMar 11 '13 at 23:55

It's not a duplicate because the criteria in the second column does not necessarily apply to the whole cell. Chuff's solution solved the problem though, thank you for your replay nonetheless. – João Mar 11 '13 at 18:48

Here's an alternative formula for getting the result you want:

``````  =SUM((A1:A5="Female")*IF(ISERROR(SEARCH("B",B1:B5)),0,1))
``````

It's an array formula, so you will need to enter it with the Control-Shift-Enter key combination.

-
Thank you very much! Worked like a charm! – João Mar 11 '13 at 18:36

Which version of Excel? In Excel 2007 or later versions you can use `COUNTIFS` with "wildcards" for column B criterion like this

`=COUNTIFS(A:A,"Female",B:B,"*B*")`

-

You could add a column with the following formula:

``````=IF(AND(A2="Female", ISERROR(FIND("B", UPPER(B2))) = FALSE), 1,0)
``````

That will display 1 if Gender is Female and the Answer contains a B (sidenote FIND returns an error if the value is not found so that is why the ISERROR is needed).

You can then copy this formula down to all rows with data. You can use the 1 to sum or count the number that match your criteria.

-
Chuff's solution solved the problem, thank you for your replay nonetheless. – João Mar 11 '13 at 18:39