Comrade Beckles
New Member
- Joined
- Nov 25, 2015
- Messages
- 4
We have a register keeping track of all our counselling referrals and I need to keep track of numbers of unallocated patients based on differing sets of criteria, as below:
<tbody>
</tbody>
Assuming as above are A, B, C,D I have COUNTIFS formulas set up for all my different requirements like so:
* patient's file is on hold - COUNTIF(D2:D5,"ON HOLD")
* patient is on the waitlist - COUNTIF(D2:D5,"WAITLIST")
* patient is not yet allocated, and is funded by Bolton - COUNTIFS(D2:D5,"",C2:C5,"Bolton")
* patient is not yet allocated, and is not funded by Bolton - COUNTIFS(D2:D5,"",C2:C5,"<>Bolton")
Each time I enter a new patient, I manually update all the formulas to indicate the new bottom row of data. To save some time (and make the register look a little more schmancy for any funding bodies who need a squizz), what's the easiest way to have the formula update automatically?
Thanks in advance
First name | Surname | Funding | Counsellor/status |
Angus | Smith | Bolton | Franklin |
Beaumont | Smith | Hitchcock | |
Cyrus | Smith | On hold | |
Dorian | Smith | Waitlist | |
Elgar | Smith | Hitchcock |
<tbody>
</tbody>
Assuming as above are A, B, C,D I have COUNTIFS formulas set up for all my different requirements like so:
* patient's file is on hold - COUNTIF(D2:D5,"ON HOLD")
* patient is on the waitlist - COUNTIF(D2:D5,"WAITLIST")
* patient is not yet allocated, and is funded by Bolton - COUNTIFS(D2:D5,"",C2:C5,"Bolton")
* patient is not yet allocated, and is not funded by Bolton - COUNTIFS(D2:D5,"",C2:C5,"<>Bolton")
Each time I enter a new patient, I manually update all the formulas to indicate the new bottom row of data. To save some time (and make the register look a little more schmancy for any funding bodies who need a squizz), what's the easiest way to have the formula update automatically?
Thanks in advance