How to Count Multiple Statuses Across Columns in Excel
Question details
The user needs to count specific attendance statuses across multiple columns for each employee, while excluding blank cells and 'Present' entries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee attendance to calculate the total number of specific negative attendance statuses (like Late, Call Out, NCNS) over a period without manually adding them up.
- Observed behavior
- The user requires a streamlined formula to aggregate counts of multiple text conditions across a horizontal row range without creating overly complex nested statements.
Ensure your attendance data range does not contain merged cells, as this can cause errors in array formulas. Verify that the status text is spelled consistently across your sheet without accidental trailing spaces.
Use SUM and COUNTIF with an Array Constant
This is the most direct method to count multiple specific text values within a range by passing an array of criteria to the COUNTIF function.
By nesting a COUNTIF function inside a SUM function and providing an array constant (values in curly braces), Excel calculates the count for each specific status and then sums the results together into one total.
Click on the cell where you want the total count for the first employee to appear (e.g., cell AA2).
Type the formula =SUM(COUNTIF(B2:Z2, {"Late","Left Early","Call Out","NCNS"})) into the formula bar.
Press Enter. If you are using an older version of Excel, you may need to press Ctrl+Shift+Enter to confirm it as an array formula.
Click the bottom-right corner of the cell and drag the fill handle down to apply this formula to the remaining employees in your list.
Subtract Unwanted Values using COUNTA and COUNTIF
If you have many statuses to count but only a few to exclude (like "Present" and blanks), it is more efficient to count all non-blank cells and subtract the ones you do not want.
Easily Track and Count Attendance Data in WPS Spreadsheet
WPS Spreadsheet offers full support for advanced array formulas, COUNTIF, and COUNTA functions, making it incredibly easy to track employee attendance and manage complex datasets.
- 1. Open your workbook: Launch WPS Spreadsheet and open your employee attendance tracker.
- 2. Select the target cell: Click on the cell at the end of the row where you want to calculate the total statuses.
- 3. Input the array formula: Type =SUM(COUNTIF(B2:Z2, {"Late","Left Early","Call Out","NCNS"})) and press Enter.
- 4. Auto-fill the column: Use the drag handle to quickly copy the formula down for the rest of your employees.
Frequently Asked Questions
Why does my COUNTIF array formula return 0 or an error?
In older versions of Excel, array formulas need to be evaluated using Ctrl+Shift+Enter instead of just Enter. Additionally, check for invisible trailing spaces in your cell data, which can prevent exact matches with your criteria.
Can I use cell references instead of typing the statuses directly into the formula?
Yes. You can list your criteria in a separate range (e.g., AA1:AA4). Then, use the formula =SUMPRODUCT(COUNTIF(B2:Z2, AA1:AA4)) to count the statuses dynamically based on those cell references.
How do I count multiple criteria with partial text matches?
You can use wildcard characters in your COUNTIF criteria. For example, adding an asterisk and typing "*Late*" will count any cell containing the word "Late", even if there is additional text in that same cell.
