+ Reply to Thread
Results 1 to 9 of 9

Count Unique value from multiple sheet with criteria

  1. #1
    Registered User
    Join Date
    08-01-2018
    Location
    Chennai
    MS-Off Ver
    2017
    Posts
    19

    Count Unique value from multiple sheet with criteria

    Hi,

    I have 2 sheets. Below is the scenario.

    In sheet1, I have a set of columns having the User IDs. ( expected outcome in sheet1. )
    In sheet2, I have the User Ids and number of account IDs. Acoount Ids can be duplicated...

    I can’t have the duplicates removed and then counted. This is a real time file and the formula should be dynamic.
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,707

    Re: Count Unique value from multiple sheet with criteria

    On sheet 2, Advanced filter and check the unique box. Have the data placed into columns F & G. then in Sheet1, in B5 =COUNTIF(Sheet2!$F$2:$F$614,Sheet1!A5) and copy down.

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Count Unique value from multiple sheet with criteria

    Another way that uses dynamic named ranges (dnrs). If you are not familiar with dnrs they automatically size themselves to fit the data. Once they are set up formulas in the spreadsheet reference those dynamic ranges by their names.

    There are two in the attached. These are formulas which are assigned names in Name Manager. They are Account_ID and Account_Owner_Alias

    Their formulas are


    G
    H
    5
    Account_ID
    =Sheet2!$B$2:INDEX(Sheet2!$B:$B,MATCH("zzzzzz",Sheet2!$B:$B,1))
    6
    Account_Owner_Alias
    =Sheet2!$A$2:INDEX(Sheet2!$A:$A,MATCH("zzzzz",Sheet2!$A:$A,1))


    Then array enter this formula in B5 of Sheet1 and fill down. If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  4. #4
    Registered User
    Join Date
    08-01-2018
    Location
    Chennai
    MS-Off Ver
    2017
    Posts
    19

    Re: Count Unique value from multiple sheet with criteria

    Thank you flame

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Count Unique value from multiple sheet with criteria

    You are welcome. Thank you for the feedback.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  6. #6
    Registered User
    Join Date
    08-01-2018
    Location
    Chennai
    MS-Off Ver
    2017
    Posts
    19

    Re: Count Unique value from multiple sheet with criteria

    FlameRetired,

    Actually, i am getting errors while applying the same formula . Please find enclosed the Orginal File ( Please consider The Data Loader as Sheet 1 and Call Data as Sheet no 2)

    In Dala Loader ShEet , I have a set of columns having the User IDs (Cell: E Column) ( expected outcome in Data Loader: K Column )
    Call Data Sheet , I have the User Ids ( D Column) and the number of account IDs (F Column). Account Ids can be duplicated...

    I canít have the duplicates removed and then counted. This is a real time file and the formula should be dynamic . Can you please apply the formula in the orginal file and send back

  7. #7
    Registered User
    Join Date
    08-01-2018
    Location
    Chennai
    MS-Off Ver
    2017
    Posts
    19

    Re: Count Unique value from multiple sheet with criteria

    Pfa i have attached the orginal file

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,707

    Re: Count Unique value from multiple sheet with criteria

    As an alternative, have you attempted my solution?

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,937

    Re: Count Unique value from multiple sheet with criteria

    Please find enclosed the Orginal File ( Please consider The Data Loader as Sheet 1 and Call Data as Sheet no 2)

    In Dala Loader ShEet , I have a set of columns having the User IDs (Cell: E Column) ( expected outcome in Data Loader: K Column )
    Call Data Sheet , I have the User Ids ( D Column) and the number of account IDs (F Column). Account Ids can be duplicated...
    I don't see a file matching that description.

+ 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 count unique value in a sheet based on multiple criteria
    By dhasa in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 08-05-2018, 12:00 PM
  2. [SOLVED] Unique name count on multiple criteria
    By sakmsb in forum Excel General
    Replies: 7
    Last Post: 09-15-2015, 05:32 AM
  3. count unique values in multiple worksheets with multiple criteria
    By craig04 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-06-2012, 03:26 PM
  4. [SOLVED] Count Unique Cells with Multiple Criteria
    By fkrueger1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-28-2012, 02:53 PM
  5. Replies: 8
    Last Post: 07-13-2012, 06:00 AM
  6. Count Unique Values, but with multiple criteria
    By the northern monkey in forum Excel General
    Replies: 3
    Last Post: 02-02-2012, 07:45 AM

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