Formula Expert Help Needed (Number of days for Oldest Ticket, Count all except blank & Closed status

Hi Smartsheet Formula experts!

I spend a lot of time scrubbing the community board for formula help and thankfully I usually find my solution. (Thank you all!)

However, I'm trying to build this sheet summary out and after about 2 + hours, I'm reaching out to the experts in the community.

Screen shot of the metrics I am trying to capture below and a formula not working.

Oldest Request: How many days has the oldest request on the list been there

Newest Request: What is the time around the most recent one.

Total Requests: Not open and status cell is not blank

Total in history: Number of requests that have come in (numbers pulled from this sheet and an intake archive sheet)

Accepted total: This is everything with a status that is not blank or "declined".

GO! :)

Find more posts tagged with

Formulas

Accepted answers

MegJF

Thank you!!

All comments

Kelly Moore

Hey @MegJF

Oldest Request (this is a text/number field) I had to guess on what was a valid 'Request' that was still in the system. I will tweak this as you desire

=TODAY()-MIN(COLLECT([Intake Submitted]:[Intake Submitted], Status:Status, OR(@cell<>"", @cell<>"Declined", @cell<>"Closed")))

Newest Request (this is a date field) - if there are conditions to what constitutes Newest Request, please define

=MAX([Intake Submitted]:[Intake Submitted])

Total Requests

=COUNTIFS(Status:Status, OR(@cell<>"", @cell<>"Open"))

Total in history You will have to build the cross sheet references - you cannot simply copy paste this formula

=[Total Requests]# + COUNTIFS({Archive Sheet Status Column}, OR(@cell<>"", @cell<>"Open"))

Accepted total

=COUNTIFS(Status:Status, OR(@cell<>"", @cell<>"Declined"))

Does this work for you?

Kelly

MegJF

Thank you!!

MeganJF

Hi Again,

New company, same formula issues. :)

Trying to collect the oldest request still set to either New Request or Reviewing Request based on the date the form was submitted.

=TODAY() - MIN(COLLECT([Date Submitted]:[Date Submitted], Status:Status, HAS(@cell "New Request", @cell "Reviewing Request")))

Same issue with the Newest form submitted

=MAX(COLLECT([Date Submitted]:[Date Submitted]))

I am getting "Incorrect Argument Set" and completely stumped why this is not working.

Genevieve P.

Hi @MeganJF

The HAS function can only take one criteria at a time, and is used for a multi-select cell. Is your Status column multi select?

Try adjusting @Kelly Moore's formula:

=TODAY()-MIN(COLLECT([Date Submitted]:[Date Submitted], Status:Status, OR(@cell = "New Request", @cell = "Reviewing Request"))

For your second formula, you don't need COLLECT 🙂 The Collect function is essentially a filter, allowing you to add values to filter in or filter out. However if you're just looking for the MAX date or most recent date in the entire column, you can just have the Date column listed in your MAX function:

=MAX([Date Submitted]:[Date Submitted])

Let us know if that sorted it out!

Cheers,

Genevieve

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.