Count unique distinct values that meet multiple criteria in closed workbook

**efernandes67** · 03-12-2012, 08:52 PM

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

**DGagnon** · 03-12-2012, 09:19 PM

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.

**efernandes67** · 03-12-2012, 09:31 PM

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)}

**Haseeb Avarakkan** · 03-12-2012, 09:51 PM

Try this...with CTRL+SHIFT+ENTER

Please Login or Register  to view this content.

**DGagnon** · 03-12-2012, 09:53 PM

this should do the trick for you then:

Please Login or Register  to view this content.

**efernandes67** · 03-13-2012, 06:05 AM

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