# Count unique distinct values that meet multiple criteria in closed workbook

1. ## 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?

Elio Fernandes

2. ## 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.``

3. ## 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. ## 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.``

5. ## 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. ## 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))

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

#### 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