Formula creation for due date based of a person.

Hi all,

Hoping I can get some guidance on how to create a formula to achieve the below?

I have the below formula to count the amount of quotations a estimator(Firstname Lastname) has quoted. This is the Total Quoted Projects cell. (see below for Quotation Metrics sheet I created. This is not the source sheet obvs).

=COUNTIFS({Estimator1}, FIND("Firstname Lastname", @cell) > 0)

How do I use the same formula but add a total count on when quotes are due in the next 5 days for this person?

And the same again but counts the amount of quotes this person has not sent yet? (so essentially blank quote sent date within the source sheet)?

I created 2 reports to try and help extract data easier but then realised you cannot use a report in a formula.

Essentially these total counts will be made into metric widgets on a dashboard designed around this one person.

QUOTES DUE NEXT 5 DAYS

QUOTES NOT SENT

There are several 10 Estimators that use this one source sheet. Sometimes there are 3/4 using at the same time. So could imagine the amount of data/info that is being added or changed throughout the day. We are looking at creating individual dashboards so we can use as a guidance/summary board for just our info and where we are at with everything. We used the filter stage at first, but with some much being added etc, it got to cumbersome to sum up every column when needed.

Thanks again!

Find more posts tagged with

Accepted answers

Heather Duff

Hi Ty,

I would suggest that you create a sheet that has each estimator's name in the primary column. We'll call that column Estimator. Add columns for Total Quoted Projects, Total Quotes Due - Next 5 Days, and Total Quotes Due - Not Sent. All columns are text/number columns. You'll be setting up cross-sheet formulas.

Here, we'll assume that {Estimator} is a range you have named for the Estimator column from the source sheet, {Due Date} is the range named for the column for the Due Date column in the source sheet, and {Date Sent} is the range named for the column for the date sent from the source sheet. Make sure that the Due Date and Date Sent columns

For the Total Quoted Projects column in your summary sheet, enter this formula:

=COUNTIF({Estimator}, Estimator@row)

For the Total Quotes Due - Next 5 Days column in your summary sheet, I'm assuming we'll want to NOT count tasks that have already been completed (already have a date in the date sent column). Here we go:

=COUNTIFS({Estimator}, Estimator@row, {Due Date}, <=TODAY(5), {Date Sent}, "")

For the Total Quotes Due - Not Sent column in your summary sheet, try this:

=COUNTIFS({Estimator}, Estimator@row, {Date Sent}, "")

Hope this helps! Let me know if you have any questions.

Best,

Heather

Heather Duff

Ty,

I'm so glad it worked out! Have a great week.

Best,

Heather

All comments

Heather Duff

Hi Ty,

For the Total Quoted Projects column in your summary sheet, enter this formula:

=COUNTIF({Estimator}, Estimator@row)

=COUNTIFS({Estimator}, Estimator@row, {Due Date}, <=TODAY(5), {Date Sent}, "")

For the Total Quotes Due - Not Sent column in your summary sheet, try this:

=COUNTIFS({Estimator}, Estimator@row, {Date Sent}, "")

Hope this helps! Let me know if you have any questions.

Best,

Heather

Ty Wickliffe

Hi @Heather D ,

Thank you for getting back to me. Wow!

Creating a new sheet based off your above made it alot more clearer so thank you.

The formula for the Total Quoted Projects worked a treat.

Formula for the "Total Quotes Due - Next 5 Days" is pulling in the incorrect total.

"Total Quotes Due - Next 5 Days" should show a total of x3 as per my source sheet after filtering so I could double check. The end result(via new created sheet) is showing x7 quotes based off this formula which is incorrect. I am in AEDT time. So the date today is 20/02/21. The dates for the quotes that are due in the next 5 days are:

22/02/21

23/02/21

25/02/21

Also the sent dates are blank as the quotes have not been sent obvs.

=COUNTIFS({Estimator}, Estimator@row, {Due Date}, <=TODAY(5), {Date Sent}, "")

Could the Date Sent section of this formula be calculating extra actions when it is not meant to?

Ty Wickliffe

@Heather D - To make is easier, See below image.

This is the source sheet where I am pulling all my data from. The Columns "Quote Due Date" and "Quote Sent Date" are date columns as these also feed out to other reports, sheets, and dashboards. So we cannot change to text/number format. I filtered this sheet to "Quote Due Date" in the next "5" days for this particular estimator only.

As you can see, There are 6x quotes that are due from todays date. 3x of them were already sent before their due date. When I use the below formula, it shows 14x quotes are due in the next 5 days? How do I get this formula to say 3x quotes are due in the next 5 days?

=COUNTIFS({Estimator}, Estimator@row, {Quote Due Date}, <=TODAY(5), {Quote Sent Date}, "")

Thanks again and hope my additional Info can help? 😕

Ty Wickliffe

All sorted @Heather D . We all got a tad confused with the filtering options in smartsheets! 🤦

Thanks heaps for you help!

Heather Duff

Ty,

I'm so glad it worked out! Have a great week.

Best,

Heather

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.