0

I need to count the number of unique entries in a column across multiple sheets in a single workbook. Not just the unique entries in one of the sheets, but comparing all sheets in the workbook for unique entries. I understand how to count unique entries in a column for each sheet, but not clear how I would compare across multiple sheets.

Here's a sample sheet: https://docs.google.com/spreadsheets/d/1kiX57tpyId3tUzDkESX6nCARYY-zV4Q4uGYRcKw12lY/edit?usp=sharing

player0
  • 124,011
  • 12
  • 67
  • 124
user5176291
  • 167
  • 2
  • 14

2 Answers2

1

Try

=ARRAYFORMULA(UNIQUE(QUERY(TO_TEXT({A:A;Sheet2!A1:A;Sheet3!A:A;Sheet4!A:A}), "select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc label Col1 'Data'")))

Props:

Mike Latch count distinct values in spreadsheet

user0 How can I create a unique list of data across multiple rows AND columns?

It occurs to me that, perhaps, you simple wanted a count of the unique terms.

If so, then this formula would suffice:

=COUNTUNIQUE({A:A;Sheet2!A:A;Sheet3!A:A;Sheet4!A:A})

The result is 12.

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
  • Thanks for the response! This isn't quite what I'm getting at. I've changed the entries in my example sheet so there is only one unique item across all sheets. The result should be 1 unique item across all 4 sheets. The rest are duplicates. Thoughts? – user5176291 Jan 11 '20 at 17:58
  • "The result should be 1 unique item across all 4 sheets. The rest are duplicates." To be honest - I don't quite understand this. Would you please modify your spreadsheet to include an example of a successful outcome. – Tedinoz Jan 12 '20 at 08:09
0

try:

=COUNTUNIQUE({Sheet1!A:A; Sheet2!A:A; Sheet3!A:A; Sheet4!A:A})

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I got "phrase 1" thru "phrase 12" - 12 in all; you you used the same code and got 8. Ah, ha. The OP changed the spreadsheet data; both are right, in the context of the data that applied. – Tedinoz Jan 12 '20 at 08:00