How to COUNTUNIQUE across multiple sheets in single workbook

0
votes

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

2
arraysgoogle-sheetsuniquegoogle-sheets-formulacount-unique

2 Answers

1
votes

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.

0
votes

try:

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

0