multiple values in a cell; count cells only once if have both of 2 val [SOLVED]

[clhendricksbc]

Hello:


First off, let me say a great, big, huge thank you to everyone involved in this site! What a fantastic resource it is, and I really appreciate the time taken by those who are willing to answer questions on the forum.


I am rather a newbie to excel, having mastered very simple formulas, and now happy to have figured out COUNTIF and COUNTIFS. I have yet to master the venerable SUMPRODUCT, and I don't know if it's relevant here.


I have searched quite a bit on the web and on this site for an answer to my question, and I apologize it is somewhere that I haven't seen. It's quite possible I'm searching on the wrong terms or looking in the wrong places.


I have done a survey that has open-ended questions, where people answer with sentences. There are five questions, and the answers to each are in a column in excel (I am using Excel 2008 for Mac). For each answer, I am assigning codes to capture similarities in answers. For example, if someone says they liked a class because it helped them make friends in university, I give that answer the code "FRND." If someone says that the classroom developed a good, close, community feel, I give it the code "COMMUN." Each answer can have several codes attached to it.


I have set up a column next to each question for the codes. So, next to the column of answers for Question 3, I have inserted a new column that I have named "Q3Answrs." Each cell of Q3Answrs has the codes for the corresponding answer in the column next to it, same row. In each cell of Q3Answrs there can be several codes, such as: FRND, WRIT, COMMUN, PROF, etc.


Now, I want to do two things, one of which I have already figured out.


1. I want to count how many times a code occurs in the column Q3Answrs, such as FRND. I have used COUNTIF for this:


=COUNTIF(Q3Answrs, "*FRND*")


I used asterisks around FRND so that the cell would be counted even if FRND had other things around it (so, e.g., if the cell had WRIT, FRND, PROF, then it would still be counted). So I've got this task done.


2. I also want to count how many cells there are that contain either or both of two codes (or three, or four...). For example, I'd like to count how many cells there are that contain FRND or COMMUN or both. But, I'd like to count each cell ONLY ONCE, even if it contains both of those codes.


I've tried two things, neither of which works. First, I tried COUNTIFS:


=COUNTIFS(Q3Answrs,"*FRND*",Q3Answrs,"*COMMUN*")


That gave me how many cells have both of those codes, but I want to know how many have either or both.


I've also tried two COUNTIF functions:


=COUNTIF(Q3Answrs, "*FRND*")+COUNTIF(Q3Answrs, "*COMMUN*")


That gave me how many cells had either or both, but it counted cells that had both twice. I want to just count them one time. I could explain why, but it would make this long post even longer. Just ask if you want to know/if it's relevant.


Now, I probably should have set it up so that each cell has only one code, and the codes are in several columns. But as I have 20 or more codes, I wanted to avoid that if possible. If it's not possible to do what I want to do with the codes all in single cells separated by commas, well, then, so be it, and I will separate them into their own columns.


Thanks so much for any help anyone can offer.

 

[xld]

This should do it for you


=SUMPRODUCT(--((ISNUMBER(SEARCH("FRND",Q3Answrs)))+(ISNUMBER(SEARCH("COMMUN",Q3Answrs)))>0))

 

[clhendricksbc]

Thanks for the reply--I will try it tomorrow. It is fairly late at night right now where I am! Will let you know if it works!

 

[clhendricksbc]

It works just fine--thank you so much! It even works with three different codes, as follows:


=SUMPRODUCT(--((ISNUMBER(SEARCH("FRND",Q3Answrs)))+(ISNUMBER(SEARCH("COMMUN",Q3Answrs)))+(ISNUMBER(SEARCH("SOCGRP",Q3Answrs)))>0))


Now, of course, I'm curious *how* it works. I am starting to get a handle on SUMPRODUCT, thanks to the great pages on this site. But I haven't yet figured out ISNUMBER or SEARCH, nor how they work together.


If not too much trouble, is there a page or two that might help me understand how this formula works? If too complicated, well, I will just be happy it works and try as I go along learning to figure it out.


Thanks again!


Oh...is this enough to mark it "solved," or should I do something else?

 

[immm]

Hi, I also work in market research and facing a similar problem that the one you were having, but I have the codes in different cells. I'm trying to count how many cells (in the same row) have either one or both codes, and only have it count it as once if one or both codes are present. Can anyone help me? Thanks!

 

[SirJB7]

@immm

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!

 

[immm]

Thanks! I have just posted it as a new topic.