How to automatically update countifs formula with multiple conditions

[Comrade Beckles]

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:

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

 

[bosco_yip]

Try :

1] patient's file is on hold :

=COUNTIF(D2:INDEX(D:D,MATCH("zzzz",A:A)),"ON HOLD")

2] patient is on the waitlist :

=COUNTIF(D2:INDEX(D:D,MATCH("zzzz",A:A)),"WAITLIST")

3] patient is not yet allocated, and is funded by Bolton :

=COUNTIFS(D2:INDEX(D:D,MATCH("zzzz",A:A)),"",C2:INDEX(C:C,MATCH("zzzz",A:A)),"Bolton")

4] patient is not yet allocated, and is not funded by Bolton :

=COUNTIFS(D2:INDEX(D:D,MATCH("zzzz",A:A)),"",C2:INDEX(C:C,MATCH("zzzz",A:A)),"<>Bolton")

Regards

 

[Comrade Beckles]

That works PERFECTLY! Thank you so much

 

[bosco_yip]

That works PERFECTLY! Thank you so much

Glad to help.