How do I create this function using COUNTIFS?

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!!

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.