• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Return 1 value if certain criteria (including or) is met [SOLVED]

I

immm

New Member
Hi,


I work in market research and I’m facing a problem that may seem trivial but I have not been able to resolve with a more simple formula. Open ended responses get coded into buckets that have a common theme, and then nets are created with those codes. Thus, I need to count as 1 if any of the codes are included in the cells (in the same row – one respondent). In this example codes 1-7, should be counted as 1 if any of those numbers are present. I have over thousands respondents to do, and will need to create several of these nets, so anyway to simplify this formula will be great.


See example of the sheet in the link and the formula I’m currently using:


https://www.dropbox.com/s/x1nj55e676ayar7/Example%20of%20Coded%20Open%20Ends.xls


1.=IF(OR(B2=1,C2=1,D2=1,E2=1,B2=2,C2=2,D2=2,E2=2,B2=3,C2=3,D2=3,E2=3,B2=4,C2=4,D2=4,E2=4,B2=6,C2=6,D2=6,E2=6,B2=7,C2=7,D2=7,E2=7),1,0)


2.I have also try to do countif formula but cannot get around the OR condition to include so that each code will only be counted as one.

Many Thanks!
 
Hi, immm!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


I was going to say that you could use this formula for column K:

=CONTAR.SI(B2:F2;1) -----> in english: =COUNTIF(B2:F2,1)

but the 1 for row 5 respondant 4 confused me. Would you please clarify how to count?


Regards!
 
Hi ,


Can you try this ?


=--(COUNTIFS(B2:F2,">=1",B2:F2,"<=7")>0)


The COUNTIFS function is only available in Excel 2007 and later.


If you cannot use the COUNTIFS function , try this :


=--(SUMPRODUCT((B2:F2>=1)*(B2:F2<=7))>0)


Narayan
 
Hi,

Not sure what is in the file uploaded by OP. But based on the formula in the original post, only values 1, 2, 3, 4, 6, 7 are allowed in B2:E2. i.e. value 5 is missing or excluded.


To make things generic, I setup a Named reference called "NumbersToCheck", referring to a range with values of 1,2,3,4,6,7.


Then you could use the following formula to return a 1 or 0 if B2:E2 has at least one match:

=N(SUMPRODUCT(COUNTIF(NumbersToCheck, B2:E2))>0)


On the other hand, if you wish to count all of your rows at once, you could use something like the following. Here List refers to a range such as B2:E50

=SUM(N(MMULT(COUNTIF(NumbersToCheck, list), TRANSPOSE(COLUMN(list)^0))>0))

entered with Ctrl + Shift + Enter


Cheers,

Sajan.
 
Hi Sajan ,


I did not go through the formula posted initially ; I generally rely on what is described in plain English ! This statement is clear :



In this example codes 1-7, should be counted as 1 if any of those numbers are present.




Just to give some more clarity to the question , here are the first few rows of data :

[pre]
Code: 
RESPONDENT   Code 1    Code 2   Code 3	Code 4	Code 5  NET Count(codes 1-7)
RESPONDENT 1	1					1
RESPONDENT 2	2	1				1
RESPONDENT 3	1	2				1
RESPONDENT4	3	4				1
RESPONDENT 5	4					1
RESPONDENT 6	17					0
RESPONDENT 7	7					1
RESPONDENT 8	12					0
RESPONDENT 9	2					1
RESPONDENT 10	2	9				1
RESPONDENT 11	7	8				1
RESPONDENT 12	19					0
RESPONDENT 13	1					1
RESPONDENT 14	10					0
RESPONDENT 15	8					0
RESPONDENT 16	1					1
RESPONDENT 17	4					1
RESPONDENT 18	1	2	8			1
[/pre]
Surprisingly , there is no 5 in the posted data , so it is impossible to decide whether it should be included or not.


Narayan
 
Hi Narayan,

Thanks for posting some of the data rows. I did see the English statement that codes can be 1-7, but then I saw that the OP went to the trouble of including a formula, and exclude the value 5 in it. So I went with the formula!


It is also interesting to note that the data rows span columns B:F, while the formula only referenced B2:E2. Not sure if "Code 5" was an "afterthought"!


In any case, we have given the OP and any other readers interested in the solution some choices!


Regards,

Sajan.
 
Hi Sajan ,


Can you please recheck your formulae ?


I created the following two names :


1. NumbersToCheck , referring to : ={1,2,3,4,5,6,7}


i.e. a row vector of 1 row and 7 columns.


2. List , referring to : $B$2:$F$111


i.e. a matrix of 110 rows and 5 columns.


I selected the range K2:K111 , and entered the following array formula , using CTRL SHIFT ENTER :


=--(MMULT(--ISNUMBER(MATCH(List,NumbersToCheck,0)),TRANSPOSE(COLUMN(List)^0))>0)


Narayan
 
This is awesome! Thank you all for your responses!!!


I was able to get the counts using this beautiful formula that Sajan sent me:


=N(SUMPRODUCT(COUNTIF(NumbersToCheck, B2:E2))>0)


I added these numbers to check (1,2,3,4 & 6,7) to the range P2:U2, so the formula looks like this:

=N(SUMPRODUCT(COUNTIF(P2:U2, B2:E2))>0) and it works nicely!!!


But, still trying to understand this other one that Sajan sent me, which will be even better because I can get the count for all respondents at once!


“On the other hand, if you wish to count all of your rows at once, you could use something like the following. Here List refers to a range such as B2:E50

=SUM(N(MMULT(COUNTIF(NumbersToCheck, list), TRANSPOSE(COLUMN(list)^0))>0))

entered with Ctrl + Shift + Enter”


For example if I need to count 1,2,3,4 & 6,7 as 1 in the range B2:E111, how will this formula will look like? I have the numbers to check in P2:U2. I tried this, but it is not working:


=SUM(N(MMULT(COUNTIF(P2:U2,B2:E111), TRANSPOSE(COLUMN(B2:E111)^0))>0))


And, sorry about this other question but when entering a formula what do you mean by “entered with Ctrl + Shift + Enter”


Thanks again!
 
Enter your formula then

Don't press Enter

Press Ctrl + Shift + Enter instead


That tells Excel to treat the formula as an Array Formula

If you look st the Formula Bar you will see your formula is wrapped in { } 's

You don't enter those Brackets Excel does for you to signify it is an Array Formula


Have a read of: http://chandoo.org/forums/topic/array-formula-tutorial

Including the links in the post
 
Hi Narayan,

I am still getting the expected results. My test data setup is as follows. Not sure if I am overlooking something.

[pre]
Code: 
Code1	Code2	Code3	Code4	Count	TotalCount
1	5	8	10	1	2
10	12	14	16	0
8	5	9	8	0
9	14	16	18	0
24	28	10	9	0
8	5	5	5	0
5	9	10	5	0
9	7	8	9	1
[/pre]
Here "Count" was obtained using the first formula, and the TotalCount was obtained using the second formula.


Your approach reproduces the Count column above, in a single operation. Instead of using ISNUMBER(MATCH(...)) combination, you could simplify it using COUNTIF().


@immm:

When entering the second formula, instead of pressing the ENTER key, press the CTRL, SHIFT and ENTER keys together. The formula will now be surrounded by {} indicating that the formula was entered as an array formula.


The formula as you wrote it should work.


Hope that helps.


Cheers,

Sajan.
 
Hi Sajan ,


I thought one used named ranges to avoid using worksheet cells ! Of course , I missed out the crucial word in this sentence of yours !


To make things generic, I setup a Named reference called "NumbersToCheck", referring to a range with values of 1,2,3,4,6,7.


Try creating the named ranges NumbersToCheck and List the way I have defined them in my earlier post , and check your formulae.


Narayan
 
Hi Narayan,

I think I now understand what you are saying... I think you are pointing out that the following formula

=N(SUMPRODUCT(COUNTIF(NumbersToCheck, B2:E2))>0)


refers to B2:E2, even though List was defined as B2:F111


You are correct. The above formula only returns a count for a single row, while your formula when array-entered over the entire K2:K111 range, provides the counts for every row in a single operation.


(I did recreate the ranges as you had in your post to see if I am missing something. But since the above formula is only for a single row's counts, it cannot be array-entered over the entire range. If individual counts are needed, your formula is the way to go. And to get the count of all of those counts, my second formula could be used.)


It is still early in the day here... so I could have missed your intent altogether!


Regards,

Sajan.
 
Hi Sajan ,


Probably my Excel is corrupted !


I define NumbersToCheck as a named range , and in the Refers To box , I put in : ={1,2,3,4,6,7}


I enter your first formula :


=N(SUMPRODUCT(COUNTIF(NumbersToCheck,B2:E2))>0)


Try it and see.


Next I change the definition of NumbersToCheck to : =Sheet1!$P$3:$U$3


where P3:U3 contain the numbers 1 , 2 , 3 , 4 , 6 and 7.


The formula remains the same , but the result is different !


With the first definition , I change the formula slightly , and it gives the correct result.


Can you let me know whether your observations are the same ?


Narayan
 
Hi Narayan,

Since COUNTIF() expects a range reference, you would not be able to supply a constant array as its input.


As expected, when I setup NumbersToCheck to a constant array by putting {1,2,3,4,6,7} through the "Refers to" box, I get a #VALUE error. That is consistent with the syntax for the COUNTIF() function.


If a constant array needs to be used, we could replace COUNTIF with N(ISNUMBER(MATCH(...))) and get the expected results.


Regards,

Sajan.
 
Hi Sajan ,


Yes , but since COUNTIF merely tells us how much range1 and range2 have in common , it does not appear to matter much if we switch the two around :


=N(SUMPRODUCT(COUNTIF(B2:E2,NumbersToCheck))>0)


works correctly.


Narayan
 
Hi Narayan,

Yes, you are correct. In this particular situation, changing the order of the arguments to COUNTIF does not make a difference since we are only interested in the SUM being greater than zero.


However, the order might be important in situations where you would want to know which of the values found a match. This is realized because COUNTIF returns results containing the same number of columns as in range 2.


Regards,

Sajan.
 
These are a lot of new concepts in excel that I’m not familiar with (i.e. Array formula), so I will work a little bit more with all the info you guys provided.


Thank you again! You have made my life much better!!! This community is great!!!
 
You must log in or register to reply here.
Back
Top