About this lesson
COUNTIFS and SUMIFS: several conditions and date ranges
COUNTIFS and SUMIFS test several conditions at once; a row counts only if it meets all of them. =SUMIFS(D2:D15,C2:C15,"Healthcare",D2:D15,">500") puts the sum range first, and a date range uses the same column twice: B2:B15,">="&DATE(2024,7,1),B2:B15,"<="&DATE(2025,6,30).
=COUNTIFS(C2:C15,"Healthcare",D2:D15,">500")What you'll practise
Screen a precedent transactions list for an M&A pitch: count and total deals by sector and size, then by financial year using dates as criteria.
4 steps · 40 points · the cells outlined in gold below
- COUNTIFS
- SUMIFS
- date criteria
- DATE
The data
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Target | Announced | Sector | Value ($m) |
| 2 | Meridian Diagnostics | 18/03/2024 | Healthcare | 412 |
| 3 | Bellbird Software | 27/05/2024 | Software | 865 |
| 4 | Kingfisher HR Systems | 11/06/2024 | Software | 497 |
| 5 | Coral Sea Day Hospitals | 01/07/2024 | Healthcare | 1,240 |
| 6 | Tidewater Payroll | 14/08/2024 | Software | 318 |
| 7 | Ashgrove Dental Group | 09/10/2024 | Healthcare | 186 |
| 8 | Quokka Analytics | 21/11/2024 | Software | 1,475 |
| 9 | Highfield Aged Care | 30/01/2025 | Healthcare | 690 |
| 10 | Ledger Lane | 12/03/2025 | Software | 542 |
| 11 | Cape Imaging | 28/04/2025 | Healthcare | 935 |
| 12 | Northline Health Records | 30/06/2025 | Software | 724 |
| 13 | Saltpan Cloud | 16/07/2025 | Software | 263 |
| 14 | Riverbend Pathology | 05/08/2025 | Healthcare | 578 |
| 15 | Brightwater Clinics | 22/09/2025 | Healthcare | 1,105 |
| 16 | ||||
| 17 | Healthcare over $500m | |||
| 18 | Their total value ($m) | |||
| 19 | Deals announced in FY25 | |||
| 20 | FY25 software value ($m) |