Formula to make a metrics sheet to build a pipleline dashboard
I have a sales tracker sheet and need to continuously pull data as new quotes are formed to show wins/ losses compared to our target in a dashboard.
Sheet has 3 columns that need to create the formula: Account type, Weighted Forecast, and Expected Quarter Close.
In real words, I want to say Weighted forescast is $1M for Foremarket and Cleanroom for Q1 (and then Q2, 3, 4) to show a bar graph like this
I need something like: SUMIFS(range, criterion_range1, criterion1, [criterion_range2, criterion2, ...])
=SUMIFS({Sheet - Sales Pipeline-Original Sales Trac Range 2}Weighted Forecast:Weighted Forecast,<$0, Account Type:, Foremarket Rep, Account Type:, Cleanroom Contractor, Expected Quarter Close:, Q1 2025)
There is an account type that should not be included in the weighted forecast.
Answers
-
=SUMIFS({Sheet - Sales Pipeline-Original Sales Trac Range 2}, {Weighted Forecast}:{Weighted Forecast},<0, {Account Type]:[Account Type], OR(@cell = "Foremarket Rep", @cell = "Cleanroom Contractor"), {Expected Quarter Close}:{Expected Quarter Close}, "Q1 2025")
Just some clean up of the syntax above. Using the OR and @cell for the criterion will sum only the values that are either/or given the @cell calculations. A couple things to look for in your formula:
- When comparing numbers, don't use symbols. (<0 instead of <$0)
- When comparing text, wrap text in quotations ("Foremarket Rep" instead of Foremarket Rep)
- Make sure all of your ranges are the same size. In your formula {Sheet - Sales Pipeline-Original Sales Trac Range 2} indicates it's referencing another sheet, none of your other ranges are written as such.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
All the information I am pulling is from another sheet, all of those columns listed are on the {Sheet - Sales Pipeline-Original Sales Trac Range 2}. Does the column need to be written out exactly like the sheet with the information? my sheet has the ":" after some names eg Account Type :
-
Try this:
=SUMIFS({Source Sheet Column To Sum}, {Source Sheet Weighted Forecast Column}, @cell < 0, {Source Sheet Account Type Column}, OR(@cell = "Foremarket Rep", @cell = "Cleanroom Contractor"), {Source Sheet Quarter Column}, @cell = "Q1 2025")
Each {Range} should be a single column, and the column selected is indicated above.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 489 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!