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({Tracker1819-SMEPayment}, {Tracker1819-SMEName}, [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 2018--2019 sheet (Tracker1819-SMEPayment), 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 2018--2019. 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?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!