+ Reply to Thread
Results 1 to 7 of 7

how to populate unique count in sheet 1 when the data is present in sheet 2.

  1. #1
    Registered User
    Join Date
    11-08-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    5

    how to populate unique count in sheet 1 when the data is present in sheet 2.

    Hi All,

    i want to get the unique count in sheet 1 using formula but please note the data is present in Sheet 2.

    Sheet 2

    Column A Column B
    A123 completed
    A456 In progress
    A789 Not Started
    A123 Completed.

    So i want to get the unique count of completed in Sheet 1.

    Thanks in advance.
    Vishnu Balan

  2. #2
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: how to populate unique count in sheet 1 when the data is present in sheet 2.

    =SUMPRODUCT(IFERROR((Sheet2!B1:B10="Completed")*1/COUNTIF(Sheet2!A1:A10,Sheet2!A1:A10),0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Click just below left if it helps, Booşathì

  3. #3
    Registered User
    Join Date
    11-08-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    5

    Re: how to populate unique count in sheet 1 when the data is present in sheet 2.

    Hi Boopathi,

    Many thanks it worked perfectly.

    I would like to add one more criteria to populate the unique count, Please help

    Example Below

    Sheet 2
    Column A Column B Column C
    A123 CAR completed
    A456 BUS In progress
    A789 TRUCK Not Started
    A123 CAR Completed

    So in Sheet 1 want to get the unique count of CAR where completed.

    Thanks again.
    Vishnu Balan

  4. #4
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: how to populate unique count in sheet 1 when the data is present in sheet 2.

    Hi Try this array

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-08-2014
    Location
    India
    MS-Off Ver
    2007
    Posts
    5

    Re: how to populate unique count in sheet 1 when the data is present in sheet 2.

    It worked perfectly fine , Thanks

    Need one more suggestion please, in sheet 1 i have not done a paste special because when the data changes in sheet 2 i want the results populate immediately in Sheet 1.

    But since my excel file has more than 40000 line items, when ever i do any changes in the file its taking a long time to calculate, is there any option to improve the speed.

    Vishnu Balan

  6. #6
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007 & 2013
    Posts
    1,454

    Re: how to populate unique count in sheet 1 when the data is present in sheet 2.

    Hi, Since it's an array formula it will take time, you can set the formula calculation mode to manual during data entry and calculate whever you need but that too will be slow,

    But i suggest you to put a helper column which will results
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    =1 in sheet2 and use normal sum formula in sheet1 will improve the speed.

    Ref the attachment..
    Attached Files Attached Files

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: how to populate unique count in sheet 1 when the data is present in sheet 2.

    Quote Originally Posted by Vishnu Balan View Post
    But since my excel file has more than 40000 line items, when ever i do any changes in the file its taking a long time to calculate, is there any option to improve the speed.
    Counting unique values on large data sets is calculation intensive.

    This array formula** should calculate faster:

    =SUM(IF(FREQUENCY(IF(Sheet2!C1:C10="Completed",IF(Sheet2!B1:B10="CAR",MATCH(Sheet2!A1:A10,Sheet2!A1:A10,0))),ROW(Sheet2!A1:A10)-ROW(Sheet2!A1)+1),1))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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] Need sheet to automatically populate next unique entry entered on a different sheet
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-23-2014, 05:15 PM
  2. Replies: 2
    Last Post: 10-22-2013, 08:04 AM
  3. Replies: 2
    Last Post: 02-13-2013, 03:14 PM
  4. [SOLVED] Count Unique function in sheet with data filter applied
    By TedH in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-28-2013, 11:39 PM
  5. [SOLVED] Excel VBA: Dropdown box selection in first sheet to populate 2nd sheet from 3rd sheet data
    By EMLalan in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-07-2012, 09:46 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