Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Count unique with multiple criteria Jo Excel Worksheet Functions 4 November 17th 09 07:20 AM
Unique count is criteria is met Neall Excel Worksheet Functions 2 June 8th 09 10:31 PM
Count unique with criteria Lindsey Excel Discussion (Misc queries) 3 April 23rd 09 07:54 PM
Count Unique with Multiple Criteria deeds Excel Worksheet Functions 3 February 14th 09 05:58 AM
Count Unique with criteria condition Spencer Hutton Excel Worksheet Functions 1 January 12th 09 07:35 PM


All times are GMT +1. The time now is 12:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"