Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Unique Value Count in 3 Columns Using Search Criteria
Hi All,
I've managed to do most of the above without any search functions, but I am getting stuck when trying to filter them by a specific value. I'd like to do this via formulas rather than any external add-ins or VBA. An outline of the data: A1-A10 John, John, Bob, Bob, Bob, Fred, Fred, Fred, Fred, George B1-B10 Smith, Smith, Smith, Smith, Smith, Doe, Doe, Doe, Doe, Doe, C1-C10 Red, Red, Red, Blue, Blue, Red, Red, Red, Blue, Blue, I want to count the unique values, filtering on the info in C1-C10. Eg I'd like to know the number of unique entries that have "Red" in C1-C10 (Answer = John Smith Red, Bob Smith Red, Fred Doe Red = 3) To do this without filtering (eg counting all "Red" and "Blue" occurances), I have used the array formula : =SUM(1/MMULT(--(A1:A10&";"&B1:B10&";"&C1:C10=TRANSPOSE(A1:A10&";" &B1:B10&";"&C1:C10)),ROW(A1:A10)^0)) Does anyone have any funky forumlas that I can use to help count the unique values whilst also having a filter? (n.b. the filter can be manually entered in a cell - eg D1 can have the value "Red" in it)? Thanks for any and all help!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count unique with multiple criteria | Excel Worksheet Functions | |||
Unique count is criteria is met | Excel Worksheet Functions | |||
Count unique with criteria | Excel Discussion (Misc queries) | |||
Count Unique with Multiple Criteria | Excel Worksheet Functions | |||
Count Unique with criteria condition | Excel Worksheet Functions |