Create a list of unique values and the times repited across in Google Sheets-CodePudding

Hi everyone 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 diferent sheets, in each sheet has diferent values but in some cases these are repited across the sheets like this:

(These are the final list after the formula.

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) and I get this:

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

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

CodePudding user response：

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'")