#UNPARCEABLE

Hi all,

I am trying to create a formula that does the following:

I would like Smartsheet to pick all the contracts that are "MSA" and "CDA/NDA" from the "Contract Type" column, and that were all signed between July 1st 2022 and June 30th 2023.

Once the conditions above are met, I would like Smartsheet to take the days that it took to draft these contracts based on the criteria above and spit out the average of days.

Essentially I want to know the average of days it took to draft MSAs and CDAs/NDAs at a given year. Can anybody help me create this formula? it's for a sheet summary.

I think this is the closer I got but I keep getting the #UNPARCEABLE error. Can anybody help me?

=IFERROR(CEILING((SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], "CDA/NDA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30)) + SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], "MSA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30))) / (COUNTIFS([Length of Drafting Contract]:[Length of Drafting Contract], "<>", [Contract Type]:[Contract Type], "CDA/NDA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30)) + COUNTIFS([Length of Drafting Contract]:[Length of Drafting Contract], "<>", [Contract Type]:[Contract Type], "MSA", [Final Executed Date]:[Final Executed Date], >=DATE(2023, 7, 1), [Final Executed Date]:[Final Executed Date], <=DATE(2024, 6, 30)))),1),"")

Find more posts tagged with

Accepted answers

All comments

Nick Korna

Hi @ben.avraham,

You can trim down your formula a bit to this:

=(SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= DATE(2022, 7, 1), @cell <= DATE(2023, 6, 30)))) / (COUNTIFS([Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= DATE(2022, 7, 1), @cell <= DATE(2023, 6, 30))))

If you're going to want to change the start/end period and you're using this in a sheet summary (for example, to show current financial/company year) then you can add a couple of extra summary fields so it's quicker to update the date range than needing to meddle with the formula to do so.

For example, here I've added 2 date summary fields, Start and End:

With the average being calculated:

=(SUMIFS([Length of Drafting Contract]:[Length of Drafting Contract], [Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= Start#, @cell <= End#))) / (COUNTIFS([Contract Type]:[Contract Type], OR(@cell = "MSA", @cell = "CDA/NDA"), [Final Executed Date]:[Final Executed Date], AND(@cell >= Start#, @cell <= End#)))

This would also enable you to easily do a rolling average if you needed other metrics as you can make the Start/End also be formulas based off today's date if necessary.

Hope this helps, but just post if you have any problems/questions!

ben.avraham

Wow thank you so much Nick! and for being so quick!

I need to show a few time periods simultaneously on a dashboard so I am not sure the calendar summary fields would be appropriate but this is a cool way that I didn't even realize!

One more question, how can I round up the result to a full number? I kept trying to integrate it into your formula but it didn't work. for example, if I got 30.1 I'd like it to say 31 for the first formula you wrote. If possible.

Again thank you so much!

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.