Count Unique Values across more files

[Andrew87..]

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.

[ashishmehra2010]

Hi Andrew,

I suggest to go for Advance Filter which is simple but one of the most powerful feature of excel.

Or you can post a sample file to get fast result.

Regards,
AM

[Andrew87..]

Hi,

thanks for your reply. I know that advanced filter would be simple and quick, but I need these files to be counted together automatically rather often, which is why a function would be great. Find attached an example of source1, source2 and overall Summary file. Counting unique values from each file separately works well as shown in the file, but I couldn't get this to count uniques across both files.

Thanks for your help.


Summary.xlsx
source1.xlsx
source2.xlsx

[oeldere]

For excel 2013 with an pivot table, see the attached file.

[Andrew87..]

Hi,

thank you, appreciated, this is certain one solution, however why I am asking about to have it done by formulas is to avoid having to put together 2 files and instead have just 1 summary file that I can open any time and have the unique values calculated without having to consolidate the files. Is there a way?

[oeldere]

To my opinion it is much easier to have all data on 1 sheet (in 1 workbook).

[Andrew87..]

It is of course and would solve many issues, but if more people are working on something on daily basis and i want to see the total count. I know it is rather odd request this one and it is no problem if it is not possible. Sum of both unique cells is mostly for 98 - 99% percent accurate, but I was just wondering if there is a way to have uniques from both combined.