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
Best Answer
-
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)
Answers
-
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)
-
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
-
Happy to help! Have fun with it.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!