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

Smartsheet example.png

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.

Find more posts tagged with

Sheets

Formulas

Dashboards

Community Platform

Cell linking

Accepted answers

All comments

Jason Tarpinian

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

JohnsonHM86

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 :

Paul Newcome

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.

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.