digest
Short Summary
Main recommendation: Use COUNTIFS with four criteria pairs (a start-date test, an end-date test, a shift number, and a non-empty result test) to count results entered for a particular shift within a date range. The video also shows how to name columns with Create from Selection so the formula is easier to write and read, and how to express "not empty" as a criterion.
Key Points
- The goal is to count nonblank result cells that fall within a start and end date and match a given shift number.
- COUNTIFS accepts one or more criteria range and criteria pairs, so the four conditions can all be handled in one formula.
- Date range is handled with two tests on the same date column: greater than or equal to the start date and less than or equal to the end date, so both endpoints are included.
- Named ranges (date, shift, result) make the formula easier to write and read.
- A "not empty" count can be written as a criterion using the "not equal" operator construction, which COUNTIFS reads as non-blank.
Details Worth Noting
- Concrete instructions:
- Highlight the top row of headers (date, shift, result) and go to Formulas > Defined Names > Create from Selection (shortcut Control+Shift+F3). In the dialog, check only the top row so each column becomes a named range, then click OK.
- Build the formula in this order: COUNTIFS(date, ">="&start_date, date, "<="&end_date, shift, shift_number, result, criteria), then press Control+Enter.
- Put comparative operators in double quotes and join cell references with the ampersand (&). The greater-than sign comes before the equal sign, for example ">=".
- For the result column, either use ">=0" (assuming numeric results that are zero or greater) or use the "not empty" construction "<>" in double quotes, which counts any non-blank entry, including text such as "two".
- Copy the formula down with the fill handle (double-click the small green square) and check the last cell with F2 to confirm references point to the correct rows.
- The video notes that a count such as "three" for shift two should be verified by hand against the data, and that the "not empty" version counts text entries that the ">=0" version would skip.
- The video assumes all results are zero or greater; a negative value would be missed by the ">=0" version.
Bottom Line
A single COUNTIFS formula with named ranges can count shift results across a date window, and switching the result test from ">=0" to "not empty" makes it count any entered value, including text.
