Help with Collecting Data for Metrics w/Multiple Criteria

I need help to collect a sum of financial information based on a range of dates in the future (or past)

SmartSheet Name: Renewals-Active
Column Names:
'MRC' is a text/number column and contains the financial information
'Estimated Contract Exp. Date' is the date column that contains dates (past, today and future)
'Primary Salesperson' is the text/number column that contains the contact for the renewal.

The information from these metrics will be on another spreadsheet that stores the project's metrics for use on a dashboard. The column ranges will be named similar to the column name

There are two metrics I have unsuccessfully been able to calculate (new user of SmartSheets).

Metric 1: Total of the 'MRC' that is from today thru the next 90 day (I will probably changes this for additional metrics too).
Metric 2: Total of the 'MRC' that is from today thru the next 90 days for a 'Primary Salesperson

'Thanks in advance for your help. I've spent too many hours working on this, and I know I have my IF, AND, OR, etc a mess.

--Tamie

Find more posts tagged with

Formulas

Accepted answers

KPH

Hi @TamieC

Thank you for the very clear brief.

The functions you need are SUMIF and its friend SUMIFS.

Metric 1

=SUMIF([Estimated Contract Exp. Date]:[Estimated Contract Exp. Date], <TODAY(90), MRC:MRC)

This means SUM all the values in MRC where the Estimated Contract Exp. Date is before TODAY plus 90 days.

For your other metrics you can change the 90 to another number.

Metric 2

=SUMIFS(MRC:MRC, [Estimated Contract Exp. Date]:[Estimated Contract Exp. Date], <TODAY(90), [Primary Salesperson]:[Primary Salesperson], "Gareth Keenan")

This will SUM all the values in MRC where the Estimated Contract Exp. Date is before TODAY plus 90 days and the Primary Salesperson is Gareth Keenan. You can replace that name with whoever you want or reference a cell that contains the name.

=SUMIFS(MRC:MRC, [Estimated Contract Exp. Date]:[Estimated Contract Exp. Date], <TODAY(90), [Primary Salesperson]:[Primary Salesperson], [column of names]@row)

All comments

KPH

Hi @TamieC

Thank you for the very clear brief.

The functions you need are SUMIF and its friend SUMIFS.

Metric 1

=SUMIF([Estimated Contract Exp. Date]:[Estimated Contract Exp. Date], <TODAY(90), MRC:MRC)

This means SUM all the values in MRC where the Estimated Contract Exp. Date is before TODAY plus 90 days.

For your other metrics you can change the 90 to another number.

Metric 2

=SUMIFS(MRC:MRC, [Estimated Contract Exp. Date]:[Estimated Contract Exp. Date], <TODAY(90), [Primary Salesperson]:[Primary Salesperson], "Gareth Keenan")

=SUMIFS(MRC:MRC, [Estimated Contract Exp. Date]:[Estimated Contract Exp. Date], <TODAY(90), [Primary Salesperson]:[Primary Salesperson], [column of names]@row)

TamieC

Thanks not only for the solution, but why the solution will work! It will help me adapt it to other situations as I learn more and think "hmmmm, can it do this"

--Tamie

KPH

Happy to help! Have fun with it.

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.