SUMIFS in a Dashboard or Report that references another sheet

MattS · 03/04/19

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

Mike Wilday · 03/04/19

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 · 03/05/19

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 · 03/05/19

Good point Paul!

Paul Newcome · 03/06/19

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.

SUMIFS in a Dashboard or Report that references another sheet

Comments

Help Article Resources

Categories