How to SUM corresponding cells from different SHEETS

Volodymyr L

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?

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.

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

@Volodymyr L

The solution may be a more complex formula.

@Andrée Starå @Genevieve P.

Volodymyr L

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

Genevieve P.

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