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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]First name
[/TD]
[TD]Surname
[/TD]
[TD]Funding
[/TD]
[TD]Counsellor/status
[/TD]
[/TR]
[TR]
[TD]Angus
[/TD]
[TD]Smith
[/TD]
[TD]Bolton
[/TD]
[TD]Franklin
[/TD]
[/TR]
[TR]
[TD]Beaumont
[/TD]
[TD]Smith
[/TD]
[TD]Hitchcock
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cyrus
[/TD]
[TD]Smith
[/TD]
[TD][/TD]
[TD]On hold
[/TD]
[/TR]
[TR]
[TD]Dorian
[/TD]
[TD]Smith
[/TD]
[TD][/TD]
[TD]Waitlist
[/TD]
[/TR]
[TR]
[TD]Elgar
[/TD]
[TD]Smith
[/TD]
[TD]Hitchcock
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.