Hi all,

I am trying to count unique values from 2 sheets located in different files.

For 1 source only the following formula works:

=SUMPRODUCT(IFERROR(1/COUNTIFS('xxxxlocationofthefile.xlsx]December'!$G$2:$G$2000;'xxxxlocationofthefile.xlsx]December'!$G$2:$G$2000);0))

(instead of xxxlocation there is actual path)

For the second file I tried the following, however that doesn't count it correctly:

=SUMPRODUCT(IFERROR(1/COUNTIFS('xxxxlocationofthefile.xlsx]December'!$G$2:$G$2000;'xxxxlocationofthefile.xlsx]December'!$G$2:$G$2000;'yyyyyyylocationofthefile.xlsx]December'!$G$2:$G$2000;'yyyyyyylocationofthefile.xlsx]December'!$G$2:$G$2000);0))

(both formulas are array formulas)

There may be duplicate values that are shared in both files, that is why I cannot do simply sum of both individually. It has to check for unique values across both files. For instance if file xx has values 1,2,2,3,4,5 and file yy has 1,5,6,6,7, 7, the total count of unique values would be 7. With sum of both it would be 9, which is what I don't want.

Thank you very much for your help.