Formula COUNTIFS, criteria in multiple columns and MTD

Sheryl Roland

I have a sheet that lists every call for service that shows the Date, Crew1, Crew2, if it was a transport, and if it wasn't, the reason for not transporting. I'm trying to get a formula to work that counts the total calls that a crew member ran in the current month and how many of those calls did they transport. I can't get anything to work.

Total Calls - Only count Cancel Reason <NONE> and Patient Refused Transport

Total Transports - Only count Cancel Reason <NONE>

The Director wants non-transport percentage for each crew member for the current month as well as yesterday. The top picture is all the data. The second picture is what I need but I need it just for the current month and yesterday.

*I do have another helper column that is a checkbox if it is the current month if that is easier.

Thanks,

Sheryl

SteyJ

Hello @Sheryl Roland ,

Total Calls

=COUNTIFS({Cancel Reason}, {Cancel Reason}, "<NONE>", {Crew1}, Crew@row, {Current Month}, 1) + 
COUNTIFS({Cancel Reason}, {Cancel Reason}, "<NONE>", {Crew2}, Crew@row, {Current Month}, 1) + 
COUNTIFS({Cancel Reason}, {Cancel Reason}, "Patient Refused Transport", {Crew1}, Crew@row, {Current Month}, 1) +
COUNTIFS({Cancel Reason}, {Cancel Reason}, "Patient Refused Transport", {Crew2}, Crew@row, {Current Month}, 1)

Total Transports

=COUNTIFS({Cancel Reason}, {Cancel Reason}, "<NONE>", {Crew1}, Crew@row, {Current Month}, 1) + 
 COUNTIFS({Cancel Reason}, {Cancel Reason}, "<NONE>", {Crew2}, Crew@row, {Current Month}, 1)

You will want to edit the formula for current month to be whatever you need it to be.

Does this work? Let me know.

DKazatsky2

Hi @Sheryl Roland,

You can give these a try.

For Current Month

Total Calls: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, OR(@cell = "<NONE>", @cell = "Patient Refused Transport"), {Current Month}, =1) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, OR(@cell = "<NONE>", @cell = "Patient Refused Transport"), {Current Month}, =1)

Total Transports: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, ="<NONE>", {Current Month}, =1) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, ="<NONE>", {Current Month}, =1)

Total Patient Refused Transport: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, ="Patient Refused Transport", {Current Month}, =1) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, ="Patient Refused Transport", {Current Month}, =1)

For Yesterday

Total Calls: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, OR(@cell = "<NONE>", @cell = "Patient Refused Transport"), {Date}, =TODAY(-1)) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, OR(@cell = "<NONE>", @cell = "Patient Refused Transport"), {Date}, =TODAY(-1))

Total Transports: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, ="<NONE>", {Date}, =TODAY(-1)) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, ="<NONE>", {Date}, =TODAY(-1))

Total Patient Refused Transport: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, ="Patient Refused Transport", {Date}, =TODAY(-1)) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, ="Patient Refused Transport", {Date}, =TODAY(-1))

I hope this helps,

Dave