Referencing Data across Many, Many Sheets

SYSPK ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

What is the best way to do a SUMIF for data that's spread across ~50 sheets? This cannot be done with reports.

My data involves how many hours our clients work in each state. There is overlap because a client may work in more than one state. Each of our 50 state sheets shows which clients work there and how many hours they work in that specific state.

I would like to get a master list of all client names and total hours they work across all states.

I can see SUMIFS being a nightmare having to create 50 references in one formula.

Being able to do a SUMIFS off of a report that pulls all the state sheets into 1 would be the magic answer but I know that feature is unavailable.


Does anyone have an idea of how to work around this?

Thank you