Excel: Count Staff Names Mentioned in Comments
Question details
The user needs to count how many comments mention each staff member, counting an employee only once per comment even when several names appear together.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Staff names are stored in one range and comment text is stored in another range. A comment may mention multiple employees, such as “Mary, Peter, and John all did a great job.”
- Observed behavior
- The required result is one mention count per staff member, with each matching comment contributing no more than one count for that employee.
Place the staff names in a single vertical range and the comment text in a separate range or column. Make sure the comment text has been extracted into cells, because SEARCH cannot directly evaluate an Excel comment object.
Use a Microsoft 365 dynamic array formula to count each staff member
Use this method when Excel supports LET, TOROW, and dynamic array formulas. It checks every staff name against every comment and returns one count per name.
Assume staffName is a named range containing employee names and comments is a named range containing comment text. SEARCH returns a match for each name-comment pair, while BYROW adds the matches for each employee.
A comment containing several staff names contributes one match to each named employee, not multiple matches for the same employee.
Select the employee-name cells and use Formulas > Define Name to create staffName. Select the comment-text cells and create a second named range called comments.
Select the first output cell and enter =LET(isMentioned,SIGN(ISNUMBER(SEARCH(staffName,TOROW(comments),1))),BYROW(isMentioned,LAMBDA(crit,SUM(crit)))) . Press Enter to return one count for each staff name.
Compare each returned value with the comments range. For example, a comment containing “Mary, Peter, and John” increases the count for Mary, Peter, and John by one each.
Use an MMULT array formula in older Excel versions
Use this option when LET or TOROW is unavailable. The formula creates a staff-name-by-comment match matrix and sums each employee row.
Count Staff Mentions in Comments with WPS Spreadsheet
WPS Spreadsheet can analyze comment text stored in cells with the same array-based approach. Use a dynamic formula when supported, or use a SUMPRODUCT helper formula to count comments containing each employee name once.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the staff-name and comment-text ranges.
- 2. Prepare the comment text: Place the extracted comment text in one column and employee names in another column. Select the ranges and use Formulas > Define Name if you want to use staffName and comments.
- 3. Enter a counting formula: For a simple per-employee count, select the result cell beside the first name and enter =SUMPRODUCT(--ISNUMBER(SEARCH(A2,$B$2:$B$100))) , replacing A2 and B2:B100 with your name and comment ranges.
- 4. Fill the results: Press Enter, then drag the fill handle down beside the remaining staff names. Each result counts matching comments once for that employee.
Frequently Asked Questions
Can one comment increase the count for several employees?
Yes. If a comment contains Mary, Peter, and John, the formula adds one to each employee's count. It does not treat the comment as belonging to only one person.
How do I count each employee only once in the same comment?
Use ISNUMBER(SEARCH(name,comments)) to create a true-or-false match for each name and comment, then sum the results. A Boolean match contributes one regardless of how many times the name appears in that comment.
What should I do if LET or TOROW is not available?
Use the MMULT formula =MMULT(--ISNUMBER(SEARCH(staffName,TRANSPOSE(comments))),ROW(comments)^0) and confirm it with Ctrl+Shift+Enter in older Excel versions.
Can the formula search Excel comments directly?
Usually not. Extract or copy the comment text into worksheet cells first, then use the cell range as comments or as the B2:B100 range in the formula.
How can I avoid counting partial name matches?
SEARCH matches text inside longer words, so names such as Ann may match Anna. For strict whole-name matching, standardize the comment text with delimiters or use a more advanced boundary-aware formula before counting.
