Trouble with unique formula and matching criteria from multiple columns and sheets

user_18784791 0
Viewed 33

My sheet has an agent count page that displays the names and how many times an agents name is on both sheets "IAD(Tampa)" and "Archive Docs".

=UNIQUE(QUERY({'IAD(Tampa)'!D3:D; 'Archived Docs'!D3:D},))
=COUNTIF('Archived Docs'!D3:D,A:A) + COUNTIF('IAD(Tampa)'!D3:D,A:A)

The ask is this. How can I do the above if column "H" and "Match A3:A12" are a match? Example would be DO NOT count Jesse when column "H" is Daniel because this NOT a match. Column "A" should be the name and column "B" should be the count.

SAMPLE SHEET https://docs.google.com/spreadsheets/d/1befqsGQvbPfn0XTGrygLOGcrUIMrICUagJVH0S-2rDw/edit?usp=sharing

textjoin google-query-language google-sheets arrays regex
1 Answers

try:

=QUERY({'IAD(Tampa)'!D3:H; 'Archived Docs'!D3:H}, 
 "select Col1,count(Col1) 
  where Col5 matches '"&TEXTJOIN("|", 1, Match!A2:A12)&"' 
  group by Col1 
  label count(Col1)''", )
user_56326290
Related