Formula COUNTIFS, criteria in multiple columns and MTD

Question

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 and Patient Refused Transport Total Transports - Only count Cancel Reason 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

DKazatsky2 · Answer

Hi @Sheryl Roland, You can give these a try. For Current Month Total Calls: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, OR(@cell = "", @cell = "Patient Refused Transport"), {Current Month}, =1) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, OR(@cell = "", @cell = "Patient Refused Transport"), {Current Month}, =1) Total Transports: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, ="", {Current Month}, =1) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, ="", {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 = "", @cell = "Patient Refused Transport"), {Date}, =TODAY(-1)) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, OR(@cell = "", @cell = "Patient Refused Transport"), {Date}, =TODAY(-1)) Total Transports: =COUNTIFS({Crew1}, =Crew@row, {Cancel Reason}, ="", {Date}, =TODAY(-1)) + COUNTIFS({Crew2}, =Crew@row, {Cancel Reason}, ="", {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

SteyJ · Answer

Hello @Sheryl Roland , Total Calls =COUNTIFS({Cancel Reason}, {Cancel Reason}, "", {Crew1}, Crew@row, {Current Month}, 1) + COUNTIFS({Cancel Reason}, {Cancel Reason}, "", {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}, "", {Crew1}, Crew@row, {Current Month}, 1) + COUNTIFS({Cancel Reason}, {Cancel Reason}, "", {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.