| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Unique Entries with Conditions
Say I have items in column A that are not unique. Also, say that if
something is filled in on another column (B), to count that unique item in column A. Column A is sorted. Column B may or may not be. Graphically: (A) 040000-000001 040000-000002 040000-000003 040000-000003 040000-000003 040000-000004 .... (B) COMPLETE ABORT INVALID INVALID COMPLETE COMPLETE .... ... should return 4. However, if one (or two) of the instances for 040000-000003 are null (blank), it *still* should return 4 (as one of the instances have a "mark"). In other words, the only time I should see a decrease in number of uniques in column A is if and only if every occurence of that unique in column A has a null for column B. Any ideas? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique Entries with Conditions
Hi!
Not sure I understand your last paragraph but try this and see if it does what you want: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =SUM(--(FREQUENCY(IF(B1:B6<"",MATCH(A1:A6&"",A1:A6&"",0) ),MATCH(A1:A6&"",A1:A6&"",0))0)) Biff "Rif" wrote in message ... Say I have items in column A that are not unique. Also, say that if something is filled in on another column (B), to count that unique item in column A. Column A is sorted. Column B may or may not be. Graphically: (A) 040000-000001 040000-000002 040000-000003 040000-000003 040000-000003 040000-000004 ... (B) COMPLETE ABORT INVALID INVALID COMPLETE COMPLETE ... .. should return 4. However, if one (or two) of the instances for 040000-000003 are null (blank), it *still* should return 4 (as one of the instances have a "mark"). In other words, the only time I should see a decrease in number of uniques in column A is if and only if every occurence of that unique in column A has a null for column B. Any ideas? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique Entries with Conditions
Hmm... as an array formula, it gives me a zero. I'm pretty sure I've entered
it correctly, making the necessary changes. I should be more clear. I want all "uniques" from column A but only if at least one of the instances have a word (such as COMPLETE or ABORT) in column B. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique Entries with Conditions
Weird.
When I enter a second condition outside of your FREQUENCY condition, it works. So instead of: =SUM(--(FREQUENCY(IF(B1:B6<"",MATCH(A1:A6&"",A1:A6&"",0) ),MATCH(A1:A6&"",A1:A6&"",0))0)) and converting it to =SUM((FREQUENCY(IF(Y2:Y43<"",MATCH(D2:D43&"",D2:D 43&"",0)),MATCH(D2:D43&"",D2:D43&"",0))0)*1) it gives me what I need. Does this mean I should use the other notation for arrays? I'm confused. "Rif" wrote: Hmm... as an array formula, it gives me a zero. I'm pretty sure I've entered it correctly, making the necessary changes. I should be more clear. I want all "uniques" from column A but only if at least one of the instances have a word (such as COMPLETE or ABORT) in column B. So in my example, if 040000-000003, which has 3 instances, has 1, 2 or 3 marks in column B, then I want that "unique" to be counted. If *NO* marks are present for this unique in column A, I do not want it counted. "Biff" wrote: Hi! Not sure I understand your last paragraph but try this and see if it does what you want: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =SUM(--(FREQUENCY(IF(B1:B6<"",MATCH(A1:A6&"",A1:A6&"",0) ),MATCH(A1:A6&"",A1:A6&"",0))0)) Biff "Rif" wrote in message ... Say I have items in column A that are not unique. Also, say that if something is filled in on another column (B), to count that unique item in column A. Column A is sorted. Column B may or may not be. Graphically: (A) 040000-000001 040000-000002 040000-000003 040000-000003 040000-000003 040000-000004 ... (B) COMPLETE ABORT INVALID INVALID COMPLETE COMPLETE ... .. should return 4. However, if one (or two) of the instances for 040000-000003 are null (blank), it *still* should return 4 (as one of the instances have a "mark"). In other words, the only time I should see a decrease in number of uniques in column A is if and only if every occurence of that unique in column A has a null for column B. Any ideas? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique Entries with Conditions
I should have said a resounding thank you!
Still, I don't understand why I have to add the "*1" as a second condition to make it work. Ugh. "Rif" wrote: Weird. When I enter a second condition outside of your FREQUENCY condition, it works. So instead of: =SUM(--(FREQUENCY(IF(B1:B6<"",MATCH(A1:A6&"",A1:A6&"",0) ),MATCH(A1:A6&"",A1:A6&"",0))0)) and converting it to =SUM((FREQUENCY(IF(Y2:Y43<"",MATCH(D2:D43&"",D2:D 43&"",0)),MATCH(D2:D43&"",D2:D43&"",0))0)*1) it gives me what I need. Does this mean I should use the other notation for arrays? I'm confused. "Rif" wrote: Hmm... as an array formula, it gives me a zero. I'm pretty sure I've entered it correctly, making the necessary changes. I should be more clear. I want all "uniques" from column A but only if at least one of the instances have a word (such as COMPLETE or ABORT) in column B. So in my example, if 040000-000003, which has 3 instances, has 1, 2 or 3 marks in column B, then I want that "unique" to be counted. If *NO* marks are present for this unique in column A, I do not want it counted. "Biff" wrote: Hi! Not sure I understand your last paragraph but try this and see if it does what you want: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =SUM(--(FREQUENCY(IF(B1:B6<"",MATCH(A1:A6&"",A1:A6&"",0) ),MATCH(A1:A6&"",A1:A6&"",0))0)) Biff "Rif" wrote in message ... Say I have items in column A that are not unique. Also, say that if something is filled in on another column (B), to count that unique item in column A. Column A is sorted. Column B may or may not be. Graphically: (A) 040000-000001 040000-000002 040000-000003 040000-000003 040000-000003 040000-000004 ... (B) COMPLETE ABORT INVALID INVALID COMPLETE COMPLETE ... .. should return 4. However, if one (or two) of the instances for 040000-000003 are null (blank), it *still* should return 4 (as one of the instances have a "mark"). In other words, the only time I should see a decrease in number of uniques in column A is if and only if every occurence of that unique in column A has a null for column B. Any ideas? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Unique Entries with Conditions
Either formula will work.
In your formula multiplying by 1 is the same as the double unary "--" in my formula. They're both converting booleans into numbers so that SUM can do its thing. Just to FYI: If there will be no empty cells within the range D2:D43 then you can remove all instances of &"" from the formula. That just makes it robust against empty cells in that one range. Biff "Rif" wrote in message ... Weird. When I enter a second condition outside of your FREQUENCY condition, it works. So instead of: =SUM(--(FREQUENCY(IF(B1:B6<"",MATCH(A1:A6&"",A1:A6&"",0) ),MATCH(A1:A6&"",A1:A6&"",0))0)) and converting it to =SUM((FREQUENCY(IF(Y2:Y43<"",MATCH(D2:D43&"",D2:D 43&"",0)),MATCH(D2:D43&"",D2:D43&"",0))0)*1) it gives me what I need. Does this mean I should use the other notation for arrays? I'm confused. "Rif" wrote: Hmm... as an array formula, it gives me a zero. I'm pretty sure I've entered it correctly, making the necessary changes. I should be more clear. I want all "uniques" from column A but only if at least one of the instances have a word (such as COMPLETE or ABORT) in column B. So in my example, if 040000-000003, which has 3 instances, has 1, 2 or 3 marks in column B, then I want that "unique" to be counted. If *NO* marks are present for this unique in column A, I do not want it counted. "Biff" wrote: Hi! Not sure I understand your last paragraph but try this and see if it does what you want: Entered as an array using the key combination of CTRL,SHIFT,ENTER: =SUM(--(FREQUENCY(IF(B1:B6<"",MATCH(A1:A6&"",A1:A6&"",0) ),MATCH(A1:A6&"",A1:A6&"",0))0)) Biff "Rif" wrote in message ... Say I have items in column A that are not unique. Also, say that if something is filled in on another column (B), to count that unique item in column A. Column A is sorted. Column B may or may not be. Graphically: (A) 040000-000001 040000-000002 040000-000003 040000-000003 040000-000003 040000-000004 ... (B) COMPLETE ABORT INVALID INVALID COMPLETE COMPLETE ... .. should return 4. However, if one (or two) of the instances for 040000-000003 are null (blank), it *still* should return 4 (as one of the instances have a "mark"). In other words, the only time I should see a decrease in number of uniques in column A is if and only if every occurence of that unique in column A has a null for column B. Any ideas? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| "COUNTU" function in Excel to count unique entries in a range | Excel Worksheet Functions | |||
| Count Unique Entries | Excel Worksheet Functions | |||
| Best way to get a list of unique entries in a field | Excel Worksheet Functions | |||
| count unique with conditions | Excel Worksheet Functions | |||
| Counting unique entries in column A but only if specific values appear in columns B and C | Excel Worksheet Functions | |||
Linear Mode
