How to SUM corresponding cells from different SHEETS

Good day!

I have a Sheets with hours of each workers in cells in different Sheets ( =projects)

I want to know the SUM of hours for each worker depends on STATUS

Unfortunately my formula works only for specific Column. And for each Column i have to change it

( I will sum my formula for each sheet-project)

Is it possible to make $column somehow for this formula?


Answers

  • Meg Y
    Meg Y ✭✭✭✭✭✭

    @Volodymyr L

    Not sure if I am interpreting your question correctly.

    What I have done is copy the formula into each column and change the column name in the formula. If possible, I convert it to a column formula. If not, then I drag the formula down.

    =SUMIFS({Airship_Finished}, {Airship Team}; Team@row) and so forth.

    Meg Young
    mmyoungconsulting@gmail.com

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Volodymyr L
    Volodymyr L ✭✭✭

    Hi Meg Y

    Yes, your answer works if you change formula for each column. I want to drag formula to both, column and row.

    I’m lazy and want to know, is it possible…

    without any reports and so on. Just like in Excel. A1 + A1. And drag

  • Meg Y
    Meg Y ✭✭✭✭✭✭

    @Volodymyr L

    The solution may be a more complex formula.

    @Andrée Starå @Genevieve P.

    Meg Young
    mmyoungconsulting@gmail.com

    If this answer resolves your question, please help the Community by marking it as an accepted answer. I'd also be grateful for your response - "Insightful"or "Awesome" reactions are much appreciated. Thanks!

  • Volodymyr L
    Volodymyr L ✭✭✭
    edited 11/03/22

    Also, i have this idea but it doesnt` work

    To sum INDEX

    =INDEX({Airship Status}, MATCH (TEAM@row, {Airship Team}, 0)) + the same for another projects

    It doesn`t work right now.. not sure why

  • Hi @Volodymyr L

    There currently isn't a way to make a cross-sheet formula range dynamic as you drag it across columns. The simplest way to do this would be to create one new cross-sheet reference per column to SUM. This would also make it easier to edit any of the formulas down the line!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!