SUM or COUNTIF across sheets
I have a Smartsheet for each fiscal year, and will continue to add new sheets for each FY.
On a separate sheet, I want to find the sum of values that appears in all of the FY sheets. For example, if I want to sum the total amount an employee makes on my Summary Sheet, referencing data on multiple FY sheets, I know I can just keep adding to the formula.
=SUM(COLLECT({Tracker1819SMEPayment}, {Tracker1819SMEName}, [Last + First]@row))
With this formula, it finds the sum of all of the SME (subject matter expert) payments made to an employee in FY 20182019 sheet (Tracker1819SMEPayment), given the Last+First name of an employee.
This works fine for one FY sheet. When I add a new FY sheet, I imagine I just add the same SUM statement, but adjust the ranges using the new sheet. Then again for the next FY...and so on.
For example, John Smith may have received several payments in FY 20182019. His name will appear again in the next FY sheet, so I would add the sum of amounts from both FY sheets in my Summary Sheet.
Is there by chance any easier way to do this to lead to a cleaner formula?
Comments

Unfortunately not. Since we can't use xsheet references on reports, you are going to have to tack on the next formula for each sheet the way you described.
Help Article Resources
Categories
Check out the Formula Handbook template!