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!!!
|