Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • Community Champion
    edited 01/23/25

    =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:

    1. When comparing numbers, don't use symbols. (<0 instead of <$0)
    2. When comparing text, wrap text in quotations ("Foremarket Rep" instead of Foremarket Rep)
    3. 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 :

  • Community Champion

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions