+ Reply to Thread
Results 1 to 14 of 14

Count unique distinct values IF....

  1. #1
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Question Count unique distinct values IF....

    Hi All

    I am really hoping someone clever can help with a solution, as I've got a real puzzler that I've been unable to crack the last 2 days! >.<

    I have a data set with account IDs from 4 separate systems. They should all be the same, but sadly they're not

    So I've added a column called 'Distinct' to alert us to any rows where the 'Acc ID's don't match.

    But (and here's the pesky bit) annoyingly some of the cells contain string values that begin with a '#'.
    I am unable to remove them from the data, so need to find a way to exclude them from the calculation in the distinct column

    I've been trying the advice here but have been unable to get anything working

    "get-digital-help.com/count-unique-distinct-values-that-meet-multiple-criteria-in-excel"
    [I'm not allowed to post proper links yet]

    Sample workbook attached
    Any help would be massively appreciated as I am totally stuck!

    All the best
    Vicky
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,139

    Re: Count unique distinct values IF....

    M11=SUM(IF(FREQUENCY(IF(Table24[@[Acc ID (1)]:[Acc ID (4)]]<>"",MATCH(Table24[@[Acc ID (1)]:[Acc ID (4)]],Table24[@[Acc ID (1)]:[Acc ID (4)]],0)),COLUMN(Table24[@[Acc ID (1)]:[Acc ID (4)]])-COLUMN([@[Acc ID (1)]])+1),1))


    Control+shift+enter

    copy down

  3. #3
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Count unique distinct values IF....

    Hi CarraCalla

    Thanks for the quick response!
    I tried pasting that formula in (with ctrl + shift + ent) but it's producing the same results?

    Am I doing something something wrong?

    All the best
    V
    Attached Images Attached Images

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,139

    Re: Count unique distinct values IF....

    M11=SUM(IF(FREQUENCY(IF(Table24[@[Acc ID (1)]:[Acc ID (4)]]<>"",IF(TRIM(LEFT(Table24[@[Acc ID (1)]:[Acc ID (4)]],1))<>"#",MATCH(Table24[@[Acc ID (1)]:[Acc ID (4)]],Table24[@],0))),COLUMN(Table24[@[Acc ID (1)]:[Acc ID (4)]])-COLUMN([@[Acc ID (1)]])+1),1))


    Control+shift+enter

    copy down

  5. #5
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Count unique distinct values IF....



    That's fantastic!!
    I can't tell you what a headache this has resolved for us
    Thank you very much!

  6. #6
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,139

    Re: Count unique distinct values IF....

    You are welcome

  7. #7
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Question Re: Count unique distinct values IF....

    Hi All,

    Sorry I thought this one was resolved but actually I'm having trouble copying the amazing formula that CARACALLA provided, into to my 'Clients Workbook'

    The formula works great in the dummy data doc I uploaded yesterday called Book1.xlsx

    But when I try to implement it into my Clients workbook it returns '1' for every row which is incorrect on rows where not all the account refs match

    I'd be super grateful if anyone can see where I'm going wrong.

    Any help would be appreciated
    All the best
    Vicky
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,809

    Re: Count unique distinct values IF....

    The reason appears to be is that the column bit refers to the column in the table and you have 4 more columns in the table before the Acc ID (1), if you delete these columns the formula works!

    However I have not thought how to amend the table to avoid doing this or why it should error!

  9. #9
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Count unique distinct values IF....

    Hi davsth

    Good call. Your're absolutely right, it's the preceding columns that are causing the formula to not work.

    Does anyone know why this is happening?

    Is there any way to amend the formula to still work with preceding columns?
    Attached Images Attached Images
    Attached Files Attached Files

  10. #10
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,139

    Re: Count unique distinct values IF....

    M6=SUM(IF(FREQUENCY(IF(Table15[@[SN0]:[kh]]<>"",IF(TRIM(LEFT(Table15[@[SN0]:[kh]],1))<>"#",MATCH(Table15[@[SN0]:[kh]],Table15[@[SN0]:[kh]],0))),COLUMN(Table15[@[SN0]:[kh]])-COLUMN([@SN0])+1),1))


    Control+shift+enter

    copy down
    Attached Files Attached Files
    Last edited by CARACALLA; 02-26-2020 at 11:36 AM.

  11. #11
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Count unique distinct values IF....

    Hi CARACALLA

    You must have the patience of a saint
    Apologies my last post was poorly worded

    Is there any way to amend the formula to allow for preceding columns, but still keeping the array range the same (those 4 columns [Acc ID (1):Acc ID (4)]?

    *sorry*

  12. #12
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,139

    Re: Count unique distinct values IF....

    M6=SUM(IF(FREQUENCY(IF((G6:J6)<>"",IF(TRIM(LEFT(G6:J6,1))<>"#",MATCH(G6:J6,G6:J6,0))),COLUMN(G6:J6)-COLUMN(G6)+1),1))

    Control+shift+enter

    copy down
    Last edited by CARACALLA; 02-26-2020 at 12:49 PM.

  13. #13
    Registered User
    Join Date
    10-27-2012
    Location
    Uk
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: Count unique distinct values IF....

    This is perfect! And I'm not getting the circular ref error anymore either. Thank you so much CARACALLA!

  14. #14
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,139

    Re: Count unique distinct values IF....

    You are welcome

+ 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. Formula to extract unique values/distinct values in rows in excel
    By bjnockle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-07-2020, 05:35 PM
  2. [SOLVED] How to count unique distinct values from a column where values in other column exists ?
    By Daniel Tou in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-19-2017, 05:48 PM
  3. [SOLVED] Count Distinct and SUMIF to find unique text values
    By hilton426 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-23-2017, 08:18 AM
  4. Replies: 17
    Last Post: 04-11-2016, 11:14 PM
  5. [SOLVED] Excel - Count distinct occurences and copy unique values
    By random0munky in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-26-2015, 10:41 PM
  6. Replies: 5
    Last Post: 03-13-2012, 06:05 AM
  7. Count of distinct (unique) values by day
    By velorian in forum Excel General
    Replies: 7
    Last Post: 12-06-2011, 05:03 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