Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

1  
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
add comment

3 Answers

up vote 0 down vote accepted

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.

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

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
1  
Chuff's solution solved the problem, thank you for your replay nonetheless. –  João Mar 11 '13 at 18:39
add comment

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*")

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.