So the issue i have is on the sheet below Column G formula is the same as Column H, but it is not working, i believe it is because of the complicated text it is looking for, any help is appreciated

https://docs.google.com/spreadsheets/d/1nEeulAtoiHS810VyafJCu07EyvN6xIHjdaQo46Wb5ys/edit#gid=0

CodePudding user response：

Most likely this is due to the fact that square brackets are used in your data. Those represent a character class in regex. I changed the formula you had so that it now replaces those brackets. Result can be seen in the duplicated sheet, starting in G2. The formula I used is

=INDEX(QUERY({regexreplace('All Report'!$B$1:$B, "\[|\]",),'All Report'!$C$1:$C},"select count(Col1) where (Col1 matches '"&JOIN("|",REGEXREPLACE($M$2:$M$4, "\[|\]",))&"') and Col2='"&$A2&"' label count(Col1) ''"))

An alternative formula could be

=COUNTA(FILTER('All Report'!$B:$B, match('All Report'!$B:$B, $M$2:$M$4, 0), 'All Report'!$C:$C = $A2 ))

or even

=SUMPRODUCT(FILTER('All Report'!$C:$C, match('All Report'!$B:$B, $M$2:$M$4, 0))=$A2 )

See if that helps?