+ Reply to Thread
Results 1 to 6 of 6

Count unique distinct values that meet multiple criteria in closed workbook

  1. #1
    Registered User
    Join Date
    01-01-2005
    MS-Off Ver
    2007
    Posts
    368

    Count unique distinct values that meet multiple criteria in closed workbook

    Hi,

    See file attached file.

    With the attached file opened and the formula {=SUM(IF(($B$6:$B$10<=$H$6)*($B$6:$B$10>=$G$6);1/COUNTIFS($D$6:$D$10;$D$6:$D$10;$B$6:$B$10;"<="&$H$6;$B$6:$B$10;">="&$G$6));0)} in another workbook cell, it works ok.

    What changes do I have to do, to make it work when the attached file is closed?

    Regrads,
    Elio Fernandes
    Attached Files Attached Files
    Last edited by efernandes67; 03-13-2012 at 06:08 AM.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Count unique distinct values that meet multiple criteria in closed workbook

    there was no check for condition "F", the bellow shoudl work for you

    note: array formula to be confirmed with Ctrl+Shift+Enter
    Please Login or Register  to view this content.
    Last edited by DGagnon; 03-12-2012 at 09:30 PM.
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Registered User
    Join Date
    01-01-2005
    MS-Off Ver
    2007
    Posts
    368

    Re: Count unique distinct values that meet multiple criteria in closed workbook

    Sorry I copied the wrong formula.

    Instead of eDate and sDate I am using DATE function.

    {=SUM(IF(([exf_UniqVal.xlsx]Sheet2!$C$6:$C$10="F")*([exf_UniqVal.xlsx]Sheet2!$B$6:$B$10<=DATE(2011;1;14))*([exf_UniqVal.xlsx]Sheet2!$B$6:$B$10>=DATE(2011;1;1));1/COUNTIFS([exf_UniqVal.xlsx]Sheet2!$C$6:$C$10;"F";[exf_UniqVal.xlsx]Sheet2!$D$6:$D$10;[exf_UniqVal.xlsx]Sheet2!$D$6:$D$10;[exf_UniqVal.xlsx]Sheet2!$B$6:$B$10;"<="&DATE(2011;1;14);[exf_UniqVal.xlsx]Sheet2!$B$6:$B$10;">="&DATE(2011;1;1)));0)}

  4. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,200

    Re: Count unique distinct values that meet multiple criteria in closed workbook

    Try this...with CTRL+SHIFT+ENTER

    Please Login or Register  to view this content.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Count unique distinct values that meet multiple criteria in closed workbook

    this should do the trick for you then:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    01-01-2005
    MS-Off Ver
    2007
    Posts
    368

    Re: Count unique distinct values that meet multiple criteria in closed workbook

    Thanks you all. Hasseb I am using now your formula, but I had to put a reference to the closed workbook in ROW function as shown below.

    =SUM(IF(FREQUENCY(IFERROR(IF([exf_UniqVal.xlsx]Sheet1!$C$6:$C$10="F";IF([exf_UniqVal.xlsx]Sheet1!$B$6:$B$10>=$G$6;IF([exf_UniqVal.xlsx]Sheet1!$B$6:$B$10<=$H$6;MATCH([exf_UniqVal.xlsx]Sheet1!$D$6:$D$10;[exf_UniqVal.xlsx]Sheet1!$D$6:$D$10;0))));"");ROW([exf_UniqVal.xlsx]Sheet1!$D$6:$D$10)-ROW([exf_UniqVal.xlsx]Sheet1!$D$6)+1);1))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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