I85PREP
⌘ K
MO-210 study materials
MO-210 study resources

The Roster That Counted Sixty People Twice Too Few

MO-210 · Put your knowledge to work in context.

All case studies

The Roster That Counted Sixty People Twice Too Few

An HR analyst keeps an 850-row roster on a sheet named Roster: A EmployeeID, B Department, C Start Date, D Salary. A named range EmpID was created while Roster was the active sheet, with Roster chosen in the Name Manager's Scope box. Sixty rows in column C were pasted from an HR portal; they look like 14-Mar-23, are left-aligned, and each carries a trailing space. The formula =SUMIFS(Roster!D:D,Roster!C:C,">="&DATE(2023,1,1),Roster!C:C,"<="&DATE(2023,12,31)) comes out exactly 60 salaries short of a manual count. On the Summary sheet, =COUNTA(EmpID) returns #NAME?, although the same formula works on Roster itself. Next week 40 corrected rows must be pasted over A900:D940 while column E holds existing formulas.

1. Diagnose why the year-to-date SUMIFS is exactly 60 rows short, and describe a reliable conversion path plus a verification step.

Read the worked answer

Diagnosis. The pasted values are text, not serial dates. In comparisons, Excel ranks all text as greater than any number. So a text "date" passes the first criterion (>= 1 Jan 2023, because text beats every number) but fails the second (<= 31 Dec 2023). The two criteria together act as a between-test, and every text row silently drops out - exactly the 60 pasted rows.

Confirm before fixing.

  • =ISNUMBER(C2) returns FALSE for the bad rows.
  • =COUNTIF(Roster!C:C,"<="&DATE(2023,12,31)) will be 60 lower than COUNTA of the column.

Conversion.

  • Best: select column C, Data > Text to Columns > Finish with the column formatted as Date (MDY). This rewrites all cells in place.
  • Or in a helper column: =DATEVALUE(TRIM(C2)), check with ISNUMBER, then copy and Paste Special > Values over column C and delete the helper.

Verification. Re-run the SUMIFS and compare against a manual total for two or three departments. Format column C as a date so future text entries are visibly wrong (they stay left-aligned).

2. Why does COUNTA(EmpID) return #NAME? on Summary when it works on Roster, and how should the name be fixed without breaking the existing formulas?

Read the worked answer

Why. EmpID was created with its scope set to the sheet Roster, not to the workbook. A worksheet-level name is visible only on that sheet. Anywhere else, Excel does not recognise the token and reports #NAME?.

Fix, keeping existing formulas valid.

  1. Formulas > Name Manager. The name appears with "Roster" in the Scope column.
  2. Either edit it to workbook scope (the Scope dropdown at the top of the New Name dialog is not editable on an existing name - so delete the Roster-scoped name and recreate EmpID with Scope = Workbook, pointing at the same range), or qualify the reference on Summary as Roster!EmpID.
  3. Re-enter nothing: existing EmpID references on Roster keep resolving, because the workbook name is now visible there too.

Trap to mention. If you instead add a workbook-scoped EmpID and leave the local one in place, Roster keeps using its local name and the two can point at different ranges. Delete the local one. Check the refers-to box after editing so the name still covers the whole column, including future rows.

3. How should the 40 corrected rows be pasted onto A900:D940 so that the column E formulas, formats and validation survive?

Read the worked answer

Risks in the naive approach. Select A900 and paste: if the copied block is wider than four columns it spills into E and destroys formulas. Copied formatting and data validation also overwrite the destination's.

Recommended steps.

  1. Select A900 only (not a row, not E), so the paste anchors at A900 and expands right and down.
  2. Paste with Paste Special > Values (or use "Match Destination Formatting" from the paste options) to protect formats and validation in D and E.
  3. If E was overwritten anyway, drag or double-click the fill handle on the formula in E899 to restore it down to E939.
  4. Check the pasted block for duplicate IDs: =SUMPRODUCT(--(COUNTIF(A:A,A900:A939)>1)) should be 0 before you trust any headcount.
  5. Confirm the last row is 939 (Ctrl+End) and that no stray block landed below.

If the roster is later converted to an Excel table, pasting directly under the last data row lets the table auto-expand and inherits the calculated column automatically.