About this lesson
IF, COUNTIF and SUMIF: screening acquisition targets
=IF(C2<=3,"Pass","Fail") returns one of two results depending on a test. =COUNTIF(D2:D7,"Pass") counts the matching rows and =SUMIF(D2:D7,"Pass",B2:B7) adds up their revenue.
=IF(C2<=3,"Pass","Fail")What you'll practise
Flag which targets pass a leverage test, then count them and total their revenue.
3 steps · 30 points · the cells outlined in gold below
- IF
- comparison operators
- COUNTIF
- SUMIF
The data
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Target | Revenue ($m) | Net debt / EBITDA | Screen | Targets passing | ||
| 2 | Harbour Logistics | 185 | 1.2 | Revenue passing ($m) | |||
| 3 | Southern Clinics | 72 | 3.8 | ||||
| 4 | Coastline Foods | 240 | 2.1 | ||||
| 5 | Ironbark Mining Svcs | 39 | 4.6 | ||||
| 6 | Metro Software | 156 | 3.1 | ||||
| 7 | Pacific Packaging | 98 | 0.4 |