Count number of occurrences of a word across multiple sheets in google sheets

Question

Is there a way to count the number of occurences of a word across multiple sheets?

for example i have the following data

Sheet1
ANIMALS
cat
cat
cat
dog
mouse

Sheet2
ANIMALS
cat
dog
dog
elephant

In sheet 3 i want this to appear

ANIMALS     COUNT
cat         4
dog         3
mouse       1
elephant    1

it should also be dynamic so when i add an entry in either sheet 1 and 2 it will appear in sheet 3

im using the following formula in sheet 3 but its not working as how i imagined it would be

=ArrayFormula(QUERY({Sheet1!A2:A1000,Sheet2!A2:A1000},"select Col1, count(Col2) where Col1 != '' group by Col1 label Col1 'animals', count(Col2) 'Count'"))

is there a way to do this?

marikamitsos marikamitsos · Accepted Answer · 2020-09-20T13:30:20

You need to replace {Sheet1!A2:A1000,Sheet2!A2:A1000} with {Sheet1!A2:A1000;Sheet2!A2:A1000}

So your formula becomes

=QUERY({Sheet1!A2:A1000;Sheet2!A2:A1000},"select Col1, count(Col1) where Col1 != '' group by Col1 label Col1 'animals', count(Col1) 'Count'")

Using ; instead of , you stack one column on top of the other.

Count number of occurrences of a word across multiple sheets in google sheets

1 Answers