| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count Unique Items
I want to count the unique items in a list where a related field matches
another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
You need to use an auxillary column C.
Put this formula into C1. Notice where the dollar signs ($) are located. =SUMPRODUCT(--(A$1:A1="orange"),--(B$1:B1=B1)) This formula will put a 1 the first time a person with orange appears and puut 2 the 2nd time the person appears on the list. Then you only have to count the number of 1's with this formula =COUNTIF(C1:C11,1) "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
Or without a helper column
=SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<"")) Mike "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
Mike and Joel --
Thank you for your quick response! They both worked like a charm. Have a great day. "Mike H" wrote: Or without a helper column =SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<"")) Mike "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
The formulas suggested worked great on my test data, but when I extended it
to my real data, I got the wrong result (the answer was less than one). All of my ranges are okay. Any thoughts? Thanks. "Noel" wrote: Mike and Joel -- Thank you for your quick response! They both worked like a charm. Have a great day. "Mike H" wrote: Or without a helper column =SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<"")) Mike "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
post your amended formula
"Noel" wrote: The formulas suggested worked great on my test data, but when I extended it to my real data, I got the wrong result (the answer was less than one). All of my ranges are okay. Any thoughts? Thanks. "Noel" wrote: Mike and Joel -- Thank you for your quick response! They both worked like a charm. Have a great day. "Mike H" wrote: Or without a helper column =SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<"")) Mike "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
Hi,
Try this variation =SUMPRODUCT(--(A1:A100="Orange")/--(COUNTIF(B1:B100,B1:B100&""),--(B1:B100<""))) untested Cheers, Shane Devenshire "Noel" wrote: The formulas suggested worked great on my test data, but when I extended it to my real data, I got the wrong result (the answer was less than one). All of my ranges are okay. Any thoughts? Thanks. "Noel" wrote: Mike and Joel -- Thank you for your quick response! They both worked like a charm. Have a great day. "Mike H" wrote: Or without a helper column =SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<"")) Mike "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
Excel won't accept that as a formula!
-- Biff Microsoft Excel MVP "Cheers," wrote in message ... Hi, Try this variation =SUMPRODUCT(--(A1:A100="Orange")/--(COUNTIF(B1:B100,B1:B100&""),--(B1:B100<""))) untested Cheers, Shane Devenshire "Noel" wrote: The formulas suggested worked great on my test data, but when I extended it to my real data, I got the wrong result (the answer was less than one). All of my ranges are okay. Any thoughts? Thanks. "Noel" wrote: Mike and Joel -- Thank you for your quick response! They both worked like a charm. Have a great day. "Mike H" wrote: Or without a helper column =SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<"")) Mike "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
If a name that is associated with "orange" is also associated with another
location you'll get incorrect results. Using the OP's sample data, change one of the names for "la" to Bob (which is also associated with "orange"). Try this array formula** (does not account for empty cells): =SUM(--(FREQUENCY(IF(A1:A11="orange",MATCH(B1:B11,B1:B11, 0)),ROW(B1:B11)-MIN(ROW(B1:B11))+1)0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Mike H" wrote in message ... Or without a helper column =SUMPRODUCT((A1:A100="Orange")/COUNTIF(B1:B100,B1:B100&"")*(B1:B100<"")) Mike "Noel" wrote: I want to count the unique items in a list where a related field matches another criteria. For example, A B 1 orange bob 2 orange sue 3 orange hank 4 la ted 5 la ted 6 la ted 7 la joe 8 orange bill 9 orange deb 10 orange sue 11 orange bill I want to count how many unique people there are in Orange. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count Unique Items
Yet another way.
Excel 2007 No formulas or code: http://www.mediafire.com/file/ngny5c...12_03_08a.xlsx |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use SUM and FREQUENCY functions to count unique items | Excel Discussion (Misc queries) | |||
| Use SUM and FREQUENCY functions to count unique items | Excel Discussion (Misc queries) | |||
| Count unique items in range | Excel Worksheet Functions | |||
| Count unique items in groups | Excel Worksheet Functions | |||
| How do I set up a formula to count only unique items in a column? | Excel Worksheet Functions | |||
Hybrid Mode
