Google Sheets单元格标签对比统计需求：实现Agent与QA标签匹配数量计算

Hey there! Let's figure out how to count those matching tags between your Agent and QA columns in Google Sheets—no need to stress over the formula logic, I'll walk you through it clearly.

First, let's assume your data is structured like this (adjust column references if yours are different):

• Agent tags are in column A (starting at A2, with A1 as the header)
• QA tags are in column B (starting at B2, with B1 as the header)
• Tags in each cell are separated by a comma + space (e.g., refund, late delivery, missing item). If your tags use a different separator (like semicolons or just commas with no space), we can tweak the formula later.

Formula to Get Total Matching Tags Across All Rows #

If you want a single number that counts all matching tags between Agent and QA columns (every time an Agent's tag appears in the corresponding QA cell, it adds to the total), use this formula in an empty cell (like C1):

=SUMPRODUCT(ARRAYFORMULA(COUNTIF(SPLIT(B2:B, ", "), SPLIT(A2:A, ", "))))

Let me break down what each part does so you understand:

• SPLIT(A2:A, ", "): Takes each cell in the Agent column and splits the text into individual tags based on the comma + space separator.
• SPLIT(B2:B, ", "): Does the same for the QA column's tags.
• COUNTIF(...): Checks how many times each Agent tag shows up in the corresponding QA tag list—each match gets a count of 1, no match gets 0.
• ARRAYFORMULA: Makes sure the formula runs across the entire column instead of just a single cell, so you don't have to drag it down.
• SUMPRODUCT: Adds up all those individual match counts to give you the total number of matching tags (which you're treating as errors).

Tweaks for Edge Cases #

1. Ignore Case Sensitivity: If your tags might have mixed cases (e.g., Refund vs refund) and you want these to count as matches, use the LOWER function to normalize everything:

   =SUMPRODUCT(ARRAYFORMULA(COUNTIF(LOWER(SPLIT(B2:B, ", ")), LOWER(SPLIT(A2:A, ", ")))))
   

2. Count Matches Per Row: If you want to see how many matching tags there are for each individual row (instead of a total), enter this formula in C2 and drag it down to other rows:

   =SUMPRODUCT(COUNTIF(SPLIT(B2, ", "), SPLIT(A2, ", ")))
   

3. Different Separator: If your tags use something other than , (like just commas , or semicolons ;), replace the ", " in the SPLIT functions with your actual separator. For example, if tags are tag1,tag2,tag3, use "," instead.

Give this a try, and let me know if you need to adjust it for your specific sheet layout!

内容的提问来源于stack exchange，提问作者egflame