| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMPRODUCT to count items with duplicates where another column contains two defined items
Ok, I think this is a SUMPRODUCT delima.
A B C D z 1 q 1 z 5 w 0 z 2 q 2 I'd like to get a count of duplicate values in column A that have both 1 and 2 values in column C. For example, "z" shows up three times in column A. Respectively "1" and "2" show up. That would count as one. Also, "q" exists twice in column A. Again respectively "1" and "2" shows up. That would also count as one. So the result for this formula would be 2 for my example. However, my data is much more. Any ideas. Note, the values in column A are unknown. The number of times each value may show could be as many as 4. Thanks, Paul -- |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT to count items with duplicates where another column con
=SUM(N(FREQUENCY(IF((C1:C6=1)+(C1:C6=2),MATCH(A1:A 6,A1:A6,0)),MATCH(A1:A6,A1:A6,0))0))
ctrl+shift+enter, not just enter "PCLIVE" wrote: Ok, I think this is a SUMPRODUCT delima. A B C D z 1 q 1 z 5 w 0 z 2 q 2 I'd like to get a count of duplicate values in column A that have both 1 and 2 values in column C. For example, "z" shows up three times in column A. Respectively "1" and "2" show up. That would count as one. Also, "q" exists twice in column A. Again respectively "1" and "2" shows up. That would also count as one. So the result for this formula would be 2 for my example. However, my data is much more. Any ideas. Note, the values in column A are unknown. The number of times each value may show could be as many as 4. Thanks, Paul -- |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT to count items with duplicates where another column con
Probably a shorter way, but I'll take a shot at it:
=SUMPRODUCT(--(MMULT(--ISNUMBER(MATCH(A1:A6&{1,2},A1:A6&C1:C6,0)),{1;1}) 1),--(MATCH(A1:A6,A1:A6,0)=ROW(A1:A6)-MIN(ROW(A1:A6))+1)) "PCLIVE" wrote: Ok, I think this is a SUMPRODUCT delima. A B C D z 1 q 1 z 5 w 0 z 2 q 2 I'd like to get a count of duplicate values in column A that have both 1 and 2 values in column C. For example, "z" shows up three times in column A. Respectively "1" and "2" show up. That would count as one. Also, "q" exists twice in column A. Again respectively "1" and "2" shows up. That would also count as one. So the result for this formula would be 2 for my example. However, my data is much more. Any ideas. Note, the values in column A are unknown. The number of times each value may show could be as many as 4. Thanks, Paul -- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT to count items with duplicates where another column
I think the OP wants a count of items that appear with both a 1 and 2 in Col
C. The results I get count how many items have either a 1 or 2 in Col C. For example, by changing the 0 for "w" to either a 1 or 2 it will get counted. "Teethless mama" wrote: =SUM(N(FREQUENCY(IF((C1:C6=1)+(C1:C6=2),MATCH(A1:A 6,A1:A6,0)),MATCH(A1:A6,A1:A6,0))0)) ctrl+shift+enter, not just enter "PCLIVE" wrote: Ok, I think this is a SUMPRODUCT delima. A B C D z 1 q 1 z 5 w 0 z 2 q 2 I'd like to get a count of duplicate values in column A that have both 1 and 2 values in column C. For example, "z" shows up three times in column A. Respectively "1" and "2" show up. That would count as one. Also, "q" exists twice in column A. Again respectively "1" and "2" shows up. That would also count as one. So the result for this formula would be 2 for my example. However, my data is much more. Any ideas. Note, the values in column A are unknown. The number of times each value may show could be as many as 4. Thanks, Paul -- |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUMPRODUCT to count items with duplicates where another column contains two defined items
If the values in C are actually numbers, then I think this will work for
you... =SUMPRODUCT((A1:A100=F1)*(C1:C100={1,2})) If, on the other hand, the values in C are text, then try this instead.... =SUMPRODUCT((A1:A100=F1)*(C1:C100={"1","2"})) And if the data in C could be either numbers or text, then try it this way... =SUMPRODUCT((A1:A100=F1)*(C1:C100={1,2,"1","2"})) Of course, change the range to encompass the maximum row number that might one day contain data. Rick "PCLIVE" wrote in message ... Ok, I think this is a SUMPRODUCT delima. A B C D z 1 q 1 z 5 w 0 z 2 q 2 I'd like to get a count of duplicate values in column A that have both 1 and 2 values in column C. For example, "z" shows up three times in column A. Respectively "1" and "2" show up. That would count as one. Also, "q" exists twice in column A. Again respectively "1" and "2" shows up. That would also count as one. So the result for this formula would be 2 for my example. However, my data is much more. Any ideas. Note, the values in column A are unknown. The number of times each value may show could be as many as 4. Thanks, Paul -- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
| How do I sort - and count - items in a column? | Excel Discussion (Misc queries) | |||
| How do I count differert items in a column only if the date in an. | Excel Worksheet Functions | |||
| Count number of items in one column that have a value in another? | Excel Worksheet Functions | |||
| Count number of unique items in a column that contains duplicates | Excel Worksheet Functions | |||
Linear Mode
