+ Reply to Thread
Results 1 to 6 of 6

Count number of unique text values based on 3 conditions?? STUCK!!!

  1. #1
    Registered User
    Join Date
    10-26-2018
    Location
    Leeds
    MS-Off Ver
    2017
    Posts
    3

    Lightbulb Count number of unique text values based on 3 conditions?? STUCK!!!

    Hi all!

    Looking for some help with an excel formula that is driving me crazy!!!

    Basically, I am trying to use a formula to detect the number of different 'Names' that appear in my table, but by having set criteria in 3 other columns.

    For instance, I want the results to only show those that also meet the criteria of Status = "Active", Family = "Williams" and Product = "Slab".

    I know that the result should be 2, (as both Marcus and Sirius match this), but I can't work it out!!!

    Anybody got any ideas???? So confused by this!!!

    Thanks in advance!!!

    Excel Screenshot.JPG

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Count number of unique text values based on 3 conditions?? STUCK!!!

    Hello and welcome to the forum.

    For instance, I want the results to only show those that also meet the criteria of Status = "Active", Family = "Williams" and Product = "Slab".
    I am assuming that you mean only count those not only show those. Try this:

    =COUNTIFS(A:A,"Active",B:B,"Williams",D:D,"Slab")

    Edit: Just realized that you are looking to count the number of unique names based on that criteria. 1 minute...

  3. #3
    Registered User
    Join Date
    10-26-2018
    Location
    Leeds
    MS-Off Ver
    2017
    Posts
    3

    Re: Count number of unique text values based on 3 conditions?? STUCK!!!

    Hi 63falcondude!

    Thanks for the welcome!

    I'm trying to count the number of different 'Names' that meet that criteria (Active, Williams, Slab). Not count all of them. Does that make sense?

    Your formula gives me the result of 4, as its counting the duplicate 'Sirius' entries, and I'd like to count them only once It should just say 2.

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Count number of unique text values based on 3 conditions?? STUCK!!!

    Try this:

    =SUM(--(FREQUENCY(IF((A2:A29="Active")*(B2:B29="Williams")*(D2:D29="Slab"),MATCH(C2:C29,C2:C29,0)),ROW(C2:C29)-ROW(C2)+1)>0)) Ctrl Shift Enter

  5. #5
    Registered User
    Join Date
    10-26-2018
    Location
    Leeds
    MS-Off Ver
    2017
    Posts
    3

    Re: Count number of unique text values based on 3 conditions?? STUCK!!!

    IT WORKS!!! Thanks so much!! I don't understand what on earth the ROW bit at the end means lol, but at least I can analyse my data now!!! Thanks!!

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Count number of unique text values based on 3 conditions?? STUCK!!!

    You're welcome.

    Here is an explanation of how that formula works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Count unique (text) values based on two criteria
    By Gijs in forum Excel General
    Replies: 4
    Last Post: 03-09-2018, 07:50 AM
  2. Replies: 2
    Last Post: 08-19-2015, 10:22 AM
  3. Repeat text count based on unique ID number
    By rn_ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-29-2015, 04:04 PM
  4. [SOLVED] Formula to count the number of unique text values from a list.
    By PritishD in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2015, 05:42 AM
  5. Replies: 12
    Last Post: 07-03-2012, 04:50 PM
  6. [SOLVED] count the number of unique values given certain conditions
    By eh308701 in forum Excel General
    Replies: 8
    Last Post: 05-31-2012, 11:48 PM
  7. Count Unique text based on multiple conditions
    By MAX258 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-13-2005, 01:44 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1