CODEWITHSUNDEEP
arraysregexgoogle-sheetsgoogle-query-languagetextjoin
Jon Beckner
Jon Beckner

Reputation: 57

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

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

Upvotes: 1

Views: 43

Answers (1)

player0
player0

Reputation: 1

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

Upvotes: 1

Related Questions