Sum cells in multiple worsheets that meet a specific criteria

[luismffaria]

I have several sheets (each a project sheet), and two aggregated sheets that would represent 2 companies (each company aggregating part of the projects).
On each aggregated sheet (company) I want to sum the production of the projects that corresponds to it.
So, I've started something like:
=SUMPRODUCT(SUMIF(INDIRECT("'"&project_codes_list&"'!B5"),$B$5,INDIRECT("'"&project_codes_list&"'!K380")))
where,
"project_code_list" would be the list of the project sheets
on cell "B5" of each project sheet I would put the name/code of the company

Problem is that it works for that specific cell K380 (which corresponds do Dec 2017), but with this formula I cannot drag it to the right to get the corresponding amounts to the following year (column L: Dec 2018, column M: Dec 2019,...) because it keeps the same K380...

Any thoughts? I would prefer not to use VBA

Thank you

 

[luismffaria]

I've also tried to do a sumif like this:
=+SUMPRODUCT(SUMIF(INDIRECT("'"&project_codes_list&"'!K371:EZ371"),K165,INDIRECT("'"&project_codes_list&"'!K380:EZ380"))*(SUM(INDIRECT("'"&project_codes_list&"'!B5"))=$B$5))
Problem is that the 2nd part where I would have the limitation is not working...

 

[dreid1011]

I have several sheets (each a project sheet), and two aggregated sheets that would represent 2 companies (each company aggregating part of the projects).
On each aggregated sheet (company) I want to sum the production of the projects that corresponds to it.
So, I've started something like:
=SUMPRODUCT(SUMIF(INDIRECT("'"&project_codes_list&"'!B5"),$B$5,INDIRECT("'"&project_codes_list&"'!K380")))
where,
"project_code_list" would be the list of the project sheets
on cell "B5" of each project sheet I would put the name/code of the company

Problem is that it works for that specific cell K380 (which corresponds do Dec 2017), but with this formula I cannot drag it to the right to get the corresponding amounts to the following year (column L: Dec 2018, column M: Dec 2019,...) because it keeps the same K380...

Any thoughts? I would prefer not to use VBA

Thank you

How about this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&project_codes_list&"'!B5"),$B$5,INDIRECT("'"&project_codes_list&"'!K"&379+COLUMNS($A$1:A1))))

 

[luismffaria]

How about this:

=SUMPRODUCT(SUMIF(INDIRECT("'"&project_codes_list&"'!B5"),$B$5,INDIRECT("'"&project_codes_list&"'!K"&379+COLUMNS($A$1:A1))))

it doesn't work... I still can't drag it across the row...

 

[dreid1011]

it doesn't work... I still can't drag it across the row...

Oh yes, my error.. I see it now.

=SUMPRODUCT(SUMIF(INDIRECT("'"&project_codes_list&"'!B5"),$B$5,INDIRECT("'"&project_codes_list&"'!"&ADDRESS(380,COLUMNS($A$1:K1)))))

 

[luismffaria]

it would work if it was to drag it across the rows. i was trying your formula and substitute the column with the address formula, but I'm not figuring out how...

 

[dreid1011]

it would work if it was to drag it across the rows. i was trying your formula and substitute the column with the address formula, but I'm not figuring out how...

See my edit in #5.

 

[luismffaria]

See my edit in #5.

It works! That's it! Thank you very much. What a help

 

[dreid1011]

It works! That's it! Thank you very much. What a help

You're welcome. I apologize for the confusion at first. My mind was thinking down instead of sideways.