Search code examples
listgoogle-sheetsuniquecountif

Create a list of unique values and the times repeated across

I'm obtaining a list of unique values with this formula:

=ArrayFormula(VLOOKUP(UNIQUE(REGEXEXTRACT(FILTER(A2:A1000,A2:A1000<>""),"\d+"))&"*",REGEXEXTRACT(A2:A1000,"\d+.+$"),1,FALSE))

I did this for 3 different sheets, in each sheet has different values but in some cases these are repeated across the sheets like this:

These are the final list after the formula:

enter image description here

After this I used this formula

=COUNTIF(Sheet3!$A$2:$A$500,A2)+COUNTIF(Sheet4!$A$2:$A$500,A2)+COUNTIF(Sheet5!$A$2:$A$500,A2)

I get this:

enter image description here

Actually it works as I want but is not a dynamic function I would like to have the list of the unique values and the times appearing across the sheets if is possible

Here is the problem with some text in the row that I have with []

enter image description here

Solution

  • Use below QUERY() formula-

    =QUERY({Sheet3!A:A;Sheet4!A:A;Sheet5!A:A},"select Col1, Count(Col1) 
where Col1 is not null 
group by Col1 
label Col1 'List of Values', Count(Col1) 'Count'")

    enter image description here