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.
In H3, count how many people left during the year from H1 to H2, including anyone who left on either of those dates.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Employee | Start date | Leave date | Year from | 01/04/2024 | |||
| 2 | Lucy | 03/04/2018 | 31/03/2025 | Year to | 31/03/2025 | |||
| 3 | Nadia | 25/01/2024 | Leavers in the year | |||||
| 4 | Kerry | 16/09/2024 | ||||||
| 5 | Ollie | 01/01/2023 | ||||||
| 6 | Ivan | 31/08/2019 | 30/04/2024 | |||||
| 7 | Sunil | 05/12/2019 | 23/04/2025 | |||||
| 8 | Rachel | 01/10/2024 | 05/01/2025 | |||||
| 9 | Jordan | 12/03/2024 | ||||||
| 10 | Marek | 26/08/2023 | 24/09/2023 | |||||
| 11 | Tanya | 25/12/2021 | ||||||
| 12 | Jess | 11/10/2020 | ||||||
| 13 | Grace | 18/12/2024 | ||||||
| 14 | Femi | 15/02/2024 | ||||||
| 15 | Will | 22/03/2018 | ||||||
| 16 | Nia | 18/11/2019 | ||||||
| 17 | Pippa | 09/11/2023 | 14/05/2024 | |||||
| 18 | Maria | 30/09/2016 | 01/04/2024 | |||||
| 19 | Hamza | 05/06/2020 | 15/02/2025 | |||||
| 20 | Priya | 01/04/2024 | ||||||
| 21 | Zoe | 07/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
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Name | Start | Left | Reason | From | 01/04/2025 | |
| 2 | Amira | 10/01/2020 | To | 31/03/2026 | |||
| 3 | Ben | 03/05/2021 | 14/06/2025 | Resigned | |||
| 4 | Cara | 01/09/2024 | 31/03/2026 | Retired | |||
| 5 | Dev | 05/01/2026 | |||||
| 6 | Ewa | 12/02/2018 | 20/02/2025 | Resigned |
=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.=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.=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
- In H3, count how many people left during the year from H1 to H2, including anyone who left on either of those dates.
- 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.
- Turnover is the number of leavers divided by the average headcount over the year. In H6, work it out from H3, H4 and H5.
- Resignations are counted separately from other leavers. In H3, count the people who resigned during the year from H1 to H2.
- 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.