SUMIFS in a Dashboard or Report that references another sheet

MattS

Hello!

I'm looking to utilize a Dashboard or Report and can't, for the life of me, figure out the best way to do this.

I need to give a summary of units sold to a customer, per product type, per year. See attached screenshot.

Questions or need additional information, just let me know.

Thanks,

Matt

Screen Shot 2019-03-04 at 3.26.44 PM.png

Find more posts tagged with

Construction

Formulas

IT & Operations

Project Management

Comments

Mike Wilday

You can use cross-sheet references to perform your sumifs in Smartsheet.

Smartsheet references all columns by their column header, unless you are using a cross sheet-reference which you would only reference one column at a time.

For more on creating cross-sheet references see:

See this video primer on creating formulas in Smartsheet: https://help.smartsheet.com/learning-track/get-started/formula-basics

Does that help?

Paul Newcome

You could also save yourself a little work and instead of using <= and >= to specify a date range, you could just use YEAR(@cell) = G$2

Mike Wilday

Good point Paul!

Paul Newcome

Thanks. I had to build out some year to date tracking for a manager that led to using MONTH(@cell) and YEAR(@cell) for the sheer convenience of it. (Also did a little bit of tracking on a weekly level, but that's a long story for another day).

It ended up being almost 1,600 points of data to report on for a department with only 6 people in it. Ugh. They also wanted it to be as simple as possible to recreate for future years and other departments.

Needless to say I figured out a few different ways to make life easier such as month number across row one and referencing that in my criteria and a separate cell containing the year for that particular sheet that is referenced in my formulas.

It made it so that all they need to do is change a name and/or change the year, and everything else updates automatically.

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.