Counting Unique entries from 3 workbooks

**Gooford** · 09-23-2009, 10:26 AM

Hi,
Any help as ever much appreciated.

I need to count the number of unique names (column A) on 3 separate worksheets.

Each worksheet represents a facility that people visit. It is possible that the same person will visit all three facilities. If this is the case I want to only count them once, even though they may have numerous entries on each.

I need to get the same result as if I was to copy and paste all of the names from three spreadsheets on to one (which isnt an option) and then count unique names.

To do this on one sheet I have been using =sumproduct((A1:A100<>"")/countif(A1:a100,A1:A100))

Thanks, hope the explanation makes sense.

**Domenic** · 09-23-2009, 11:31 AM

Download and install the free add-in Morefunc.xll. Then try...

=COUNTDIFF(ARRAY.JOIN(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100))

Otherwise, without the add-in, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(Sheet1!A2:A100<>"",1/(COUNTIF(Sheet1!A2:A100,Sheet1!A2:A100)+COUNTIF(Sheet2!A2:A100,Sheet1!A2:A100)+COUNTIF(Sheet3!A2:A100,Sheet1!A2:A100))))+SUM(IF(Sheet2!A2:A100<>"",1/(COUNTIF(Sheet1!A2:A100,Sheet2!A2:A100)+COUNTIF(Sheet2!A2:A100,Sheet2!A2:A100)+COUNTIF(Sheet3!A2:A100,Sheet2!A2:A100))))+SUM(IF(Sheet3!A2:A100<>"",1/(COUNTIF(Sheet1!A2:A100,Sheet3!A2:A100)+COUNTIF(Sheet2!A2:A100,Sheet3!A2:A100)+COUNTIF(Sheet3!A2:A100,Sheet3!A2:A100))))

Adjust the ranges, accordingly.