How do I create this function using COUNTIFS?

Hannahsamd

I am trying to create a function that calculates: % of Urgent Intakes Completed Within Two Business Days (Starting 4/1/23)

I have tried the following:

=COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent", {Finish Date}, "<=WORKDAY([[{Intake Dates}]],2)") / COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent")

=COUNTIFS({Start Date}@, >DATE(2023,4,1), {Priority}@, "Urgent", {Finish Date}@, <=WORKDAY({Start Date}@,2)) / COUNTIFS({Start Date}@, >DATE(2023,4,1), {Priority}@, "Urgent")

All of the columns are referenced form another sheet within Smartsheet. The formulas either come up as 0 (which there are more that 0) or UNPARSEABLE

Thank You!!

Paul Newcome

Looks like you have extra characters tucked in. Try this to start:

=COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent", {Finish Date}, <=WORKDAY({Intake Dates},2)) / COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent")

Hannahsamd

It still is coming up with 0, which I know is not correct cause there have been urgent tasks completed in a day. NAy other advice of a different function that might work?

Paul Newcome

What do you get for just this one:

=COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent")

?

Hannahsamd

I get 17 with that equation

and when I add the other element of the being completed (=COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent", {Stage}, "Done") )

I get 16

Hannahsamd

=COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent", {Stage}, "Done", NETWORKDAYS({Start Date}, {Finish Date}), ">=2" / COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent", {Stage}, "Done"))

I think this is closer to what I want but when adding the NETWORKDAYS in there, it says Invalid Data type

Paul Newcome

Yeah. The NETWORKDAYS piece isn't going to work that way. You need a column on the source sheet that calculates the number of days, and then you will need to reference that in your formula instead.

=COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent", {Stage}, "Done", {Helper Column}, @cell>=2) / COUNTIFS({Start Date}, >DATE(2023, 4, 1), {Priority}, "Urgent", {Stage}, "Done"))

Hannahsamd

Is there something else I could put in there instead of adding a new column?

Paul Newcome

Unfortunately not. Formulas that are referencing entire columns are unable to make comparisons the way you are wanting.