CODEWITHSUNDEEP
arraysgoogle-sheetsuniquegoogle-sheets-formulacount-unique
user5176291
user5176291

Reputation: 167

How to COUNTUNIQUE across multiple sheets in single workbook

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

Upvotes: 0

Views: 2356

Answers (2)

player0
player0

Reputation: 1

try:

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

0

Upvotes: 0

Tedinoz
Tedinoz

Reputation: 7984

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.

Upvotes: 1

Related Questions