Confident Cells

Turnover and headcount: practice exercises

COUNTIFS between dates, headcount on a date, turnover rate, EOMONTH. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

In H3, count how many people left during the year from H1 to H2, including anyone who left on either of those dates.

ABCDEFGH
1EmployeeStart dateLeave dateYear from01/04/2024
2Lucy03/04/201831/03/2025Year to31/03/2025
3Nadia25/01/2024Leavers in the year
4Kerry16/09/2024
5Ollie01/01/2023
6Ivan31/08/201930/04/2024
7Sunil05/12/201923/04/2025
8Rachel01/10/202405/01/2025
9Jordan12/03/2024
10Marek26/08/202324/09/2023
11Tanya25/12/2021
12Jess11/10/2020
13Grace18/12/2024
14Femi15/02/2024
15Will22/03/2018
16Nia18/11/2019
17Pippa09/11/202314/05/2024
18Maria30/09/201601/04/2024
19Hamza05/06/202015/02/2025
20Priya01/04/2024
21Zoe07/11/2024

How it works

To count dates in a period, give COUNTIFS the same range twice: once with ">="&H1 for the first day, and once with "<="&H2 for the last.

Headcount on a date is everyone who had started by then and hadn't left: their leave date is blank (""), or on or after that date. Turnover is the leavers in a period divided by the average headcount. EOMONTH(date, 0) gives the last day of that date's month.

=COUNTIFS(C2:C6,">="&G1,C2:C6,"<="&G2) =COUNTIFS(B2:B6,"<="&G1,C2:C6,"") =I1/AVERAGE(I2,I4) =EOMONTH(G1,0)

Counting dates in a period

Give COUNTIFS the same range twice: once for the first day and once for the last. Join each comparison to its cell with &: COUNTIFS(left,">="&G1, left,"<="&G2). Both ends are included.

Worked example

ABCDEFG
1NameStartLeftReasonFrom01/04/2025
2Amira10/01/2020To31/03/2026
3Ben03/05/202114/06/2025Resigned
4Cara01/09/202431/03/2026Retired
5Dev05/01/2026
6Ewa12/02/201820/02/2025Resigned
  1. =COUNTIFS(C2:C6,">="&G1,C2:C6,"<="&G2) in I1. Leavers between 1 April 2025 and 31 March 2026: Ben and Cara, who left on the last day. Ewa left before the year began. 2.
  2. =COUNTIFS(B2:B6,"<="&G1,C2:C6,"")+COUNTIFS(B2:B6,"<="&G1,C2:C6,">="&G1) in I2. Headcount on 1 April 2025: Amira hasn't left (blank), and Ben and Cara left later. Dev hadn't started and Ewa had gone: 3.
  3. =COUNTIFS(D2:D6,"Resigned",C2:C6,">="&G1,C2:C6,"<="&G2) in I3. Add a third condition to count only resignations in the year: Ben, 1.

Turnover

Turnover is leavers in the period ÷ the average headcount, usually the average of the start and end figures: =leavers/AVERAGE(start headcount, end headcount).

For monthly figures, EOMONTH(first of month, 0) gives the last day of that month to use as the end date.

What you'll practise

  1. In H3, count how many people left during the year from H1 to H2, including anyone who left on either of those dates.
  2. In H3, work out the headcount on H1: everyone who had started by then and either hasn't left (no leave date) or left on or after H1.
  3. Turnover is the number of leavers divided by the average headcount over the year. In H6, work it out from H3, H4 and H5.
  4. Resignations are counted separately from other leavers. In H3, count the people who resigned during the year from H1 to H2.
  5. Column G has the first day of each month. In H2, count how many people left in that month. Your formula will be copied down to H13.