Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This question already has an answer here:

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?

Thank you in advance,

share|improve this question

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

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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
up vote 0 down vote accepted

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


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

share|improve this answer
Thank you very much! Worked like a charm! – João Mar 11 '13 at 18:36

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.

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

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


share|improve this answer

Not the answer you're looking for? Browse other questions tagged .