Formula COUNTIFS, criteria in multiple columns and MTD

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

Answers

  • SteyJ
    SteyJ ✭✭✭✭✭
    edited 11/10/23

    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.

    Sincerely,

    Jacob Stey

  • DKazatsky2
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!