SUM or COUNTIF across sheets

Art Schneiderheinze

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?

Paul Newcome

Unfortunately not. Since we can't use x-sheet references on reports, you are going to have to tack on the next formula for each sheet the way you described.