|
check occurrence of items in a range
I want to check if "apple", "pear", "cherry"; "coconut" and
"apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
Try:
=SUMPRODUCT(N(COUNTIF(D5:E16, {"apple";"pear";"cherry";"coconut";"apricot"})=1) )=5 HTH Jason Atlanta, GA -----Original Message----- I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH ({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0 ))=5 works but =COUNT(MATCH ({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0 ))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands . |
http://www.mrexcel.com/articles.shtml Scroll down to "Formulas In Excel" and, in particular, pay attention to the articles on Array formulas. -- nbrcrunch |
You asked this question 4 days ago, and received some suggestions.
Did you try any of them? http://tinyurl.com/6r97z -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
Rag,
I now saw via the link you gave me (http://tinyurl.com/6r97z) your answer in the Text formula thread: From: "RagDyeR" Date: Tue, 15 Feb 2005 03:45:14 -0800 Local: Tues, Feb 15 2005 3:45 am Subject: Text fomula Strange enough it does not appear in the thread if I open it in the excel.misc NG. I tried you solution and saw that AND(COUNTIF(({list})) does the trick. Thank you very much. Jack. "RagDyeR" schreef in bericht ... You asked this question 4 days ago, and received some suggestions. Did you try any of them? http://tinyurl.com/6r97z -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
rag,
An aditional question: If I evaluate the COUNTIF(({list}) part of your function with F9 function key, it shows a resulting list (array) consisting of numbers that indicate how many times each item occurs in the range. Suppose the AND(COUNTIF(({list})) formula is in H10, which in casu results in TRUE, what should I do to get the elements of the resulting list - which I meant above - in H11 up to H16 (or als far as it takes with respect to the number of items in the original list)? Jack. "RagDyeR" schreef in bericht ... You asked this question 4 days ago, and received some suggestions. Did you try any of them? http://tinyurl.com/6r97z -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
Afraid I don't understand your question.
Would you care to re-phrase it, and post back? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jack Sons" wrote in message ... rag, An aditional question: If I evaluate the COUNTIF(({list}) part of your function with F9 function key, it shows a resulting list (array) consisting of numbers that indicate how many times each item occurs in the range. Suppose the AND(COUNTIF(({list})) formula is in H10, which in casu results in TRUE, what should I do to get the elements of the resulting list - which I meant above - in H11 up to H16 (or als far as it takes with respect to the number of items in the original list)? Jack. "RagDyeR" schreef in bericht ... You asked this question 4 days ago, and received some suggestions. Did you try any of them? http://tinyurl.com/6r97z -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
Rag,
=AND(COUNTIF(D5:E16;{"a";"b";"c"})) can be evaluated by highlighting the COUNTIF(D5:E16;{"a";"b";"c"}) part of the formula when it showes in the white slot next to the = sign right above the column headings (A, B, C etc.) and then click function key F9 (return to normal mode with the esc key). What I see then (right above the column headings A, B, C etc) is =AND({1,2,1}) indicating a is found once, b twice and c once in D5:E16. With ctrl C ctrl V this {1,2,1} can be copied to any cell, but I would like a formula (perhaps array entered?) that gives me (without copying) in cell H11 {1,2,1} or the elements 1, 2 and 1 below each other for instance in H11, H12 etc. I hope you can understand it now. Sorry for my English, I never have the opportunity to speak it, so it will look peculiar, I'm sure. Jack. "Ragdyer" schreef in bericht ... Afraid I don't understand your question. Would you care to re-phrase it, and post back? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jack Sons" wrote in message ... rag, An aditional question: If I evaluate the COUNTIF(({list}) part of your function with F9 function key, it shows a resulting list (array) consisting of numbers that indicate how many times each item occurs in the range. Suppose the AND(COUNTIF(({list})) formula is in H10, which in casu results in TRUE, what should I do to get the elements of the resulting list - which I meant above - in H11 up to H16 (or als far as it takes with respect to the number of items in the original list)? Jack. "RagDyeR" schreef in bericht ... You asked this question 4 days ago, and received some suggestions. Did you try any of them? http://tinyurl.com/6r97z -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
If I understand what you're looking for, why don't you just enter the values
in a column, and individually poll the list with separate Countif formulas? H1 to H3 = Apple, Pears, Cherry In I1 enter: =COUNTIF($D$6:$D$16,H1) And copy down to I3 That would end up giving you a display of, for example: Apple 2 Pears 1 Cherry 3 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jack Sons" wrote in message ... Rag, =AND(COUNTIF(D5:E16;{"a";"b";"c"})) can be evaluated by highlighting the COUNTIF(D5:E16;{"a";"b";"c"}) part of the formula when it showes in the white slot next to the = sign right above the column headings (A, B, C etc.) and then click function key F9 (return to normal mode with the esc key). What I see then (right above the column headings A, B, C etc) is =AND({1,2,1}) indicating a is found once, b twice and c once in D5:E16. With ctrl C ctrl V this {1,2,1} can be copied to any cell, but I would like a formula (perhaps array entered?) that gives me (without copying) in cell H11 {1,2,1} or the elements 1, 2 and 1 below each other for instance in H11, H12 etc. I hope you can understand it now. Sorry for my English, I never have the opportunity to speak it, so it will look peculiar, I'm sure. Jack. "Ragdyer" schreef in bericht ... Afraid I don't understand your question. Would you care to re-phrase it, and post back? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Jack Sons" wrote in message ... rag, An aditional question: If I evaluate the COUNTIF(({list}) part of your function with F9 function key, it shows a resulting list (array) consisting of numbers that indicate how many times each item occurs in the range. Suppose the AND(COUNTIF(({list})) formula is in H10, which in casu results in TRUE, what should I do to get the elements of the resulting list - which I meant above - in H11 up to H16 (or als far as it takes with respect to the number of items in the original list)? Jack. "RagDyeR" schreef in bericht ... You asked this question 4 days ago, and received some suggestions. Did you try any of them? http://tinyurl.com/6r97z -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
Rag,
I allready did so. But what I like is to retrieve the {.....} that you get when you use te F9 key. That could be useful in other cases also. When the question of retrieving the {......} part is solved, the next question is how to "break down" the list (or array?) in its elements, so how to get 1 2 5 from {1, 2, 5} Jack. "Ragdyer" schreef in bericht ... If I understand what you're looking for, why don't you just enter the values in a column, and individually poll the list with separate Countif formulas? H1 to H3 = Apple, Pears, Cherry In I1 enter: =COUNTIF($D$6:$D$16,H1) And copy down to I3 That would end up giving you a display of, for example: Apple 2 Pears 1 Cherry 3 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jack Sons" wrote in message ... Rag, =AND(COUNTIF(D5:E16;{"a";"b";"c"})) can be evaluated by highlighting the COUNTIF(D5:E16;{"a";"b";"c"}) part of the formula when it showes in the white slot next to the = sign right above the column headings (A, B, C etc.) and then click function key F9 (return to normal mode with the esc key). What I see then (right above the column headings A, B, C etc) is =AND({1,2,1}) indicating a is found once, b twice and c once in D5:E16. With ctrl C ctrl V this {1,2,1} can be copied to any cell, but I would like a formula (perhaps array entered?) that gives me (without copying) in cell H11 {1,2,1} or the elements 1, 2 and 1 below each other for instance in H11, H12 etc. I hope you can understand it now. Sorry for my English, I never have the opportunity to speak it, so it will look peculiar, I'm sure. Jack. "Ragdyer" schreef in bericht ... Afraid I don't understand your question. Would you care to re-phrase it, and post back? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Jack Sons" wrote in message ... rag, An aditional question: If I evaluate the COUNTIF(({list}) part of your function with F9 function key, it shows a resulting list (array) consisting of numbers that indicate how many times each item occurs in the range. Suppose the AND(COUNTIF(({list})) formula is in H10, which in casu results in TRUE, what should I do to get the elements of the resulting list - which I meant above - in H11 up to H16 (or als far as it takes with respect to the number of items in the original list)? Jack. "RagDyeR" schreef in bericht ... You asked this question 4 days ago, and received some suggestions. Did you try any of them? http://tinyurl.com/6r97z -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once in a given range. =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 works but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I heard that match() works only with a single column or row, so the second formula does not work. How to overcome this? Perhaps with an arrayed function? I do not want to work with consecutive countif's, because if my list {....}has 25 items it is very laborious and cumbersom to write 25 countif's. Jack Sons The Netherlands |
| All times are GMT +1. The time now is 12:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com