Counting unique text values

[sobeitjedi]

Hopefully this is quite a simple question ...

In a cell in sheet 1, I wish to count the number of unique entries in total in column A of sheet2, sheet3 & sheet4. Column A in these sheets contains peoples names (text field).

Can you help?

 

[Yongle]

assuming that the count is to start from A2. Here is formula to count unique values in columnA in sheet2 (increase the range if there are more than 10,000 rows)

=SUMPRODUCT((Sheet2!A2:A9999<>"")/COUNTIF(Sheet2!A2:A9999,Sheet2!A2:A9999&""))

 

[sobeitjedi]

assuming that the count is to start from A2. Here is formula to count unique values in columnA in sheet2 (increase the range if there are more than 10,000 rows)

=SUMPRODUCT((Sheet2!A2:A9999<>"")/COUNTIF(Sheet2!A2:A9999,Sheet2!A2:A9999&""))

But this will just count sheet2, right? What about column A in sheet 3 and column A in sheet 4?

 

[Yongle]

Use the same formula amended for sheet3 and sheet4 and add them together

=SUMPRODUCT((Sheet2!A2:A9999<>"")/COUNTIF(Sheet2!A2:A9999,Sheet2!A2:A9999&"")) + SUMPRODUCT((Sheet3!....) + SUMPRODUCT((Sheet4!....)

Which counts the values unique to each sheet
So if Ann appears 5 times on Sheet2, 10 times on Sheet3, 12 times on Sheet4 ... the count returns 3

Is that what you want? Or are you trying to eliminate names that are common between sheets? ... so that the count returns 1

 

[sobeitjedi]

Use the same formula amended for sheet3 and sheet4 and add them together

=SUMPRODUCT((Sheet2!A2:A9999<>"")/COUNTIF(Sheet2!A2:A9999,Sheet2!A2:A9999&"")) + SUMPRODUCT((Sheet3!....) + SUMPRODUCT((Sheet4!....)

Which counts the values unique to each sheet
So if Ann appears 5 times on Sheet2, 10 times on Sheet3, 12 times on Sheet4 ... the count returns 3

Is that what you want? Or are you trying to eliminate names that are common between sheets? ... so that the count returns 1

Yes, the latter - I want to count the unique values in total across the sheets, so in your example it would be 1 for Ann.