Function Problems

How to Count Present and WO by Name in Excel (Without Helper Cells)

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs a way to count specific text entries, such as 'Present' and 'WO' (Week Off), for individual names across multiple rows and columns, strictly without using any helper cells.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating employee attendance or shift schedules where names may appear multiple times across different rows in a 2D data matrix.
Observed behavior: The user requires a consolidated summary table that calculates the exact counts of each status type per person in separate columns.

Before you start

Ensure your dataset is consistently formatted without merged cells in the names or dates columns, and verify that text like 'Present' and 'WO' does not contain hidden trailing spaces to avoid counting errors.

Solution 1Recommended

Use SUM, MMULT, and TRANSPOSE Functions

This advanced array formula counts specific text entries across a two-dimensional range for specific names without requiring any helper columns.

The MMULT function performs matrix multiplication, which is highly effective for processing 2D arrays in Excel. By combining it with TRANSPOSE and SUM, you can cross-reference duplicate names and attendance statuses in a single robust formula.

Select the Target Cell

Click on the first cell in your summary table where you want the counted value to appear (e.g., cell B5).

Enter the Array Formula

Type the formula: =SUM(MMULT(--TRANSPOSE($A$13:$A$25=$A5),--($B$13:$L$25=B$4))). Ensure that $A$13:$A$25 corresponds to your list of names, and $B$13:$L$25 matches your daily attendance columns.

Apply the Formula

Press Enter to evaluate the formula. If you are using a much older version of Excel, you may need to press Ctrl+Shift+Enter to apply it as an array formula.

Copy Across the Summary Table

Click the bottom-right corner of the cell and drag the fill handle to copy the formula down for the remaining names, and across for the other status types.

Pay Attention to Absolute References: Make sure to use the exact absolute ($) and relative cell references as shown in the formula so it shifts correctly when you copy it across your summary grid.

Solution 2

Unpivot and Summarize using Power Query

This method is ideal for larger or more complex datasets where heavy matrix array formulas might slow down workbook calculation performance.

WPS Spreadsheet Recommendation

Effortlessly Manage Attendance Records in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions like MMULT and TRANSPOSE, as well as dynamic PivotTables, allowing you to seamlessly process complex attendance sheets without needing helper columns.

1. Open Your Workbook: Launch WPS Office and open your attendance tracking spreadsheet.
2. Target the Summary Cell: Click on the cell where you want to display the attendance count for the first person.
3. Input the Formula: Type or paste the =SUM(MMULT(--TRANSPOSE($A$13:$A$25=$A5),--($B$13:$L$25=B$4))) formula.
4. Apply and Drag: Press Enter, then use the fill handle to drag the formula across your entire summary table to populate the remaining counts.

100% compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Processes complex matrix array calculations for tasks like attendance counting smoothly.Includes a powerful built-in Power Query equivalent and dynamic PivotTables.Free, lightweight, and easy to use across Windows, Mac, and mobile platforms.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my MMULT formula return a #VALUE! error?

This error usually happens if the array dimensions do not match. For MMULT to work, the number of columns in the first array must equal the number of rows in the second array. Double-check your TRANSPOSE ranges to ensure they perfectly align.

Can I use the COUNTIFS function instead of MMULT for this task?

COUNTIFS typically requires a contiguous 1D range for criteria evaluation. Because your names are scattered on multiple rows and the data is spread across a 2D matrix, COUNTIFS would require extra helper columns. The MMULT solution gracefully avoids this.

Do I need to press Ctrl+Shift+Enter for this formula to work?

In older versions of Excel or WPS Spreadsheet, array formulas require you to confirm them with Ctrl+Shift+Enter. In newer versions that support dynamic arrays, simply pressing Enter is sufficient.

Excel & Data Problems