Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
Hello,
I have an Excel 2003 spreadsheet. In column C of one of the worksheets, there are numbers. In some cases, the numbers are unique and appear only once in the column. For example, the number 348975 appears only once in the column in cell C45. But in other cases, there are multiple instances of the same number. For example, 123456 appears in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334. I am hoping someone can help me with this problem. I want to count all instances of unique numbers in column C AND only one instance of each number that has multiple instances. In other words, using the numbers I gave as examples above, I would want to get a result of "3" (a count of 348975, 123456, and 780912) rather than "8" (a count of C45, C1, C2, C3, C4, C10, C150, and C334). One thought I had was to have another column (Q) in the worksheet that could basically identify each unique number with the number "1" and then number multiple instance. I could then count only the items with "1" in column Q. For example C1 123456 1 C2 123456 2 C3 123456 3 C4 123456 4 C10 789012 1 C45 348975 1 C150 789012 2 C334 789012 3 Thanks in advance for your time and help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
Here is an array formula that will do this.
=SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1)) Must be array-entered (Ctrl-Shift-Enter). Don't forget to replace "A2:A10" with your actual range. This formula assumes that there are only numbers in the range. You could also used Advanced Filter (DataFilterAdvanced Filter) to create a list of unique items to another column. HTH, JP On Jan 15, 11:36*am, A Newton wrote: Hello, I have an Excel 2003 spreadsheet. In column C of one of the worksheets, there are numbers. In some cases, the numbers are unique and appear only once in the column. For example, the number 348975 appears only once in the column in cell C45. But in other cases, there are multiple instances of the same number. For example, 123456 appears in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334. I am hoping someone can help me with this problem. I want to count all instances of unique numbers in column C AND only one instance of each number that has multiple instances. In other words, using the numbers I gave as examples above, I would want to get a result of "3" (a count of 348975, 123456, and 780912) rather than "8" (a count of C45, C1, C2, C3, C4, C10, C150, and C334). One thought I had was to have another column (Q) in the worksheet that could basically identify each unique number with the number "1" and then number multiple instance. I could then count only the items with "1" in column Q. For example C1 * * * * *123456 * * * * *1 C2 * * * * *123456 * * * * *2 C3 * * * * *123456 * * * * *3 C4 * * * * *123456 * * * * *4 C10 * * * * 789012 * * * * 1 C45 * * * * 348975 * * * * 1 C150 * * * *789012 * * * * 2 C334 * * * *789012 * * * * 3 Thanks in advance for your time and help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
On Jan 15, 11:41 am, JP wrote:
Here is an array formula that will do this. =SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1)) Must be array-entered (Ctrl-Shift-Enter). Don't forget to replace "A2:A10" with your actual range. This formula assumes that there are only numbers in the range. You could also used Advanced Filter (DataFilterAdvanced Filter) to create a list of unique items to another column. HTH, JP On Jan 15, 11:36 am, A Newton wrote: Hello, I have an Excel 2003 spreadsheet. In column C of one of the worksheets, there are numbers. In some cases, the numbers are unique and appear only once in the column. For example, the number 348975 appears only once in the column in cell C45. But in other cases, there are multiple instances of the same number. For example, 123456 appears in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334. I am hoping someone can help me with this problem. I want to count all instances of unique numbers in column C AND only one instance of each number that has multiple instances. In other words, using the numbers I gave as examples above, I would want to get a result of "3" (a count of 348975, 123456, and 780912) rather than "8" (a count of C45, C1, C2, C3, C4, C10, C150, and C334). One thought I had was to have another column (Q) in the worksheet that could basically identify each unique number with the number "1" and then number multiple instance. I could then count only the items with "1" in column Q. For example C1 123456 1 C2 123456 2 C3 123456 3 C4 123456 4 C10 789012 1 C45 348975 1 C150 789012 2 C334 789012 3 Thanks in advance for your time and help. JP, Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not exactly what I need only b/c because my examples were incomplete. My fault entirely. In many cases, the numbers in column C might start with a letter (v). For example: C1 v123456 1 C2 v123456 2 C3 v123456 3 C4 v123456 4 C10 789012 1 C45 348975 1 C150 789012 2 C334 789012 3 C53 v569802 1 C450 v569802 2 Any ideas? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
Hi,
If you havent found the formula to populate cells in col Q based for unique values in Col C, here's the formula that will do exactly as you want. You can then simply count the 1s in col Q to get the unique values in Col C: In first row of data (say row 1) key in this formula in Col Q: =COUNTIF($C$1:$C1,C1) Highlight all the cells in Col Q from row1 to end of data row (say row 450 in your example). Then do a Fill Down (CTRL-D). The important thing is to give the absolute and relative references exactly as shown (ie. keying in the $ symbol or leaving it out). The number of unique values in Col C can now be found by =COUNTIF($Q$1:$Q$450,1) in another results cell in the sheet. This will work if you do it by hand... For putting in a macro (ie. programming) you need a little more work, but can be done... Hope this helps. Best Regards, -feltra Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not exactly what I need only b/c because my examples were incomplete. My fault entirely. In many cases, the numbers in column C might start with a letter (v). For example: C1 v123456 1 C2 v123456 2 C3 v123456 3 C4 v123456 4 C10 789012 1 C45 348975 1 C150 789012 2 C334 789012 3 C53 v569802 1 C450 v569802 2 Any ideas? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
=SUM(IF(FREQUENCY(IF(LEN(A2:A10)0,MATCH(A2:A10,A2 :A10,0),""),
IF(LEN(A2:A10)0,MATCH(A2:A10,A2:A10,0),""))0,1)) Replace "A2:A10" with your range, don't forget to ctrl-shift-enter. HTH, JP On Jan 15, 12:03*pm, A Newton wrote: On Jan 15, 11:41 am, JP wrote: Here is an array formula that will do this. =SUM(IF(FREQUENCY(A2:A10,A2:A10)0,1)) Must be array-entered (Ctrl-Shift-Enter). Don't forget to replace "A2:A10" with your actual range. This formula assumes that there are only numbers in the range. You could also used Advanced Filter (DataFilterAdvanced Filter) to create a list of unique items to another column. HTH, JP On Jan 15, 11:36 am, A Newton wrote: Hello, I have an Excel 2003 spreadsheet. In column C of one of the worksheets, there are numbers. In some cases, the numbers are unique and appear only once in the column. For example, the number 348975 appears only once in the column in cell C45. But in other cases, there are multiple instances of the same number. For example, 123456 appears in C1, C2, C3, and C4. And 789012 might appear in C10, C150, and C334. I am hoping someone can help me with this problem. I want to count all instances of unique numbers in column C AND only one instance of each number that has multiple instances. In other words, using the numbers I gave as examples above, I would want to get a result of "3" (a count of 348975, 123456, and 780912) rather than "8" (a count of C45, C1, C2, C3, C4, C10, C150, and C334). One thought I had was to have another column (Q) in the worksheet that could basically identify each unique number with the number "1" and then number multiple instance. I could then count only the items with "1" in column Q. For example C1 * * * * *123456 * * * * *1 C2 * * * * *123456 * * * * *2 C3 * * * * *123456 * * * * *3 C4 * * * * *123456 * * * * *4 C10 * * * * 789012 * * * * 1 C45 * * * * 348975 * * * * 1 C150 * * * *789012 * * * * 2 C334 * * * *789012 * * * * 3 Thanks in advance for your time and help. JP, Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not exactly what I need only b/c because my examples were incomplete. My fault entirely. In many cases, the numbers in column C might start with a letter (v). For example: C1 * * * * *v123456 * * * * *1 C2 * * * * *v123456 * * * * *2 C3 * * * * *v123456 * * * * *3 C4 * * * * *v123456 * * * * *4 C10 * * * * 789012 * * * * 1 C45 * * * * 348975 * * * * 1 C150 * * * *789012 * * * * 2 C334 * * * *789012 * * * * 3 C53 * * * *v569802 * * * * 1 C450 * * *v569802 * * * * 2 Any ideas?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
On Jan 15, 1:54 pm, feltra wrote:
Hi, If you havent found the formula to populate cells in col Q based for unique values in Col C, here's the formula that will do exactly as you want. You can then simply count the 1s in col Q to get the unique values in Col C: In first row of data (say row 1) key in this formula in Col Q: =COUNTIF($C$1:$C1,C1) Highlight all the cells in Col Q from row1 to end of data row (say row 450 in your example). Then do a Fill Down (CTRL-D). The important thing is to give the absolute and relative references exactly as shown (ie. keying in the $ symbol or leaving it out). The number of unique values in Col C can now be found by =COUNTIF($Q$1:$Q$450,1) in another results cell in the sheet. This will work if you do it by hand... For putting in a macro (ie. programming) you need a little more work, but can be done... Hope this helps. Best Regards, -feltra Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not exactly what I need only b/c because my examples were incomplete. My fault entirely. In many cases, the numbers in column C might start with a letter (v). For example: C1 v123456 1 C2 v123456 2 C3 v123456 3 C4 v123456 4 C10 789012 1 C45 348975 1 C150 789012 2 C334 789012 3 C53 v569802 1 C450 v569802 2 Any ideas? Thanks! Great suggestion. That worked like a charm. All items in column Q are now appropriately numbered. One follow-up -- On another worksheet -- worksheet 2 -- I now want to tally all the items that have a value of 1 in column Q and that also have a value of "foo" in column A and "bar" in column M on worksheet 1. I thought I could find all the items that I was looking for with this formula: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet'!$Q$2:$Q$450="1")) but it is returning "0" whereas I know from looking at worksheet 1 that I should actually be getting a value of "3". This: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet'!$Q$2:$Q$450="1")) returns "6", which counts all instances without factoring in a value of "1" in column Q. Ideas? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
On Jan 15, 3:14 pm, A Newton wrote:
On Jan 15, 1:54 pm, feltra wrote: Hi, If you havent found the formula to populate cells in col Q based for unique values in Col C, here's the formula that will do exactly as you want. You can then simply count the 1s in col Q to get the unique values in Col C: In first row of data (say row 1) key in this formula in Col Q: =COUNTIF($C$1:$C1,C1) Highlight all the cells in Col Q from row1 to end of data row (say row 450 in your example). Then do a Fill Down (CTRL-D). The important thing is to give the absolute and relative references exactly as shown (ie. keying in the $ symbol or leaving it out). The number of unique values in Col C can now be found by =COUNTIF($Q$1:$Q$450,1) in another results cell in the sheet. This will work if you do it by hand... For putting in a macro (ie. programming) you need a little more work, but can be done... Hope this helps. Best Regards, -feltra Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not exactly what I need only b/c because my examples were incomplete. My fault entirely. In many cases, the numbers in column C might start with a letter (v). For example: C1 v123456 1 C2 v123456 2 C3 v123456 3 C4 v123456 4 C10 789012 1 C45 348975 1 C150 789012 2 C334 789012 3 C53 v569802 1 C450 v569802 2 Any ideas? Thanks! Great suggestion. That worked like a charm. All items in column Q are now appropriately numbered. One follow-up -- On another worksheet -- worksheet 2 -- I now want to tally all the items that have a value of 1 in column Q and that also have a value of "foo" in column A and "bar" in column M on worksheet 1. I thought I could find all the items that I was looking for with this formula: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet'!$Q$2:$Q$450="1")) but it is returning "0" whereas I know from looking at worksheet 1 that I should actually be getting a value of "3". This: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet'!$Q$2:$Q$450="1")) returns "6", which counts all instances without factoring in a value of "1" in column Q. Ideas? There was a typo in my previous examples. They should be: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet 1'!$Q$2:$Q$450="1")) and =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet 1'!$Q$2:$Q$450="1")) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to count only one instance of an item if multiple occurrences
On Jan 15, 3:53 pm, A Newton wrote:
On Jan 15, 3:14 pm, A Newton wrote: On Jan 15, 1:54 pm, feltra wrote: Hi, If you havent found the formula to populate cells in col Q based for unique values in Col C, here's the formula that will do exactly as you want. You can then simply count the 1s in col Q to get the unique values in Col C: In first row of data (say row 1) key in this formula in Col Q: =COUNTIF($C$1:$C1,C1) Highlight all the cells in Col Q from row1 to end of data row (say row 450 in your example). Then do a Fill Down (CTRL-D). The important thing is to give the absolute and relative references exactly as shown (ie. keying in the $ symbol or leaving it out). The number of unique values in Col C can now be found by =COUNTIF($Q$1:$Q$450,1) in another results cell in the sheet. This will work if you do it by hand... For putting in a macro (ie. programming) you need a little more work, but can be done... Hope this helps. Best Regards, -feltra Thanks! This is getting REALLY, REALLY CLOSE to what I need. It is not exactly what I need only b/c because my examples were incomplete. My fault entirely. In many cases, the numbers in column C might start with a letter (v). For example: C1 v123456 1 C2 v123456 2 C3 v123456 3 C4 v123456 4 C10 789012 1 C45 348975 1 C150 789012 2 C334 789012 3 C53 v569802 1 C450 v569802 2 Any ideas? Thanks! Great suggestion. That worked like a charm. All items in column Q are now appropriately numbered. One follow-up -- On another worksheet -- worksheet 2 -- I now want to tally all the items that have a value of 1 in column Q and that also have a value of "foo" in column A and "bar" in column M on worksheet 1. I thought I could find all the items that I was looking for with this formula: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet'!$Q$2:$Q$450="1")) but it is returning "0" whereas I know from looking at worksheet 1 that I should actually be getting a value of "3". This: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet'!$Q$2:$Q$450="1")) returns "6", which counts all instances without factoring in a value of "1" in column Q. Ideas? There was a typo in my previous examples. They should be: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet 1'!$Q$2:$Q$450="1")) and =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet 1'!$Q$2:$Q$450="1")) Issue solved. . . I removed the double-quotes from around the "1" like so: =SUMPRODUCT(('worksheet 1'!$A$2:$A$450="foo") * ('worksheet 1'!$M$2:$M $450="bar") * ('worksheet 1'!$Q$2:$Q$450=1)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count occurrences when two conditions are met | Excel Discussion (Misc queries) | |||
Count how many occurrences | Excel Discussion (Misc queries) | |||
how do I count occurrences of multiple conditions | Excel Worksheet Functions | |||
Count number of occurrences | Excel Discussion (Misc queries) | |||
how do I count the occurrences of multiple conditions | Excel Worksheet Functions |