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

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: 

• https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets
• https://help.smartsheet.com/videos/cross-sheet-formulas
• https://help.smartsheet.com/videos/advanced-cross-sheet-formulas
• https://www.smartsheet.com/blog/powerful-and-flexible-cross-sheet-formulas

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.