Lookup Sum of Table by Week

[estyMonsty]

Hi All,

I'm trying to look up / match the weekly sum without putting in a summary column per week.
e.g. return the sum of ('John'!)B5:H5 into cell ('Weekly Summary'!)B4. Look up value column A.

I've attached an example of what I'm trying to do.

 

[Chawbacon]

Hello Monsty,

I am assuming that you are using MS Excel. You just need to tweak it a little and put the linked cells within the parenthesis.

=SUM(John!B5:H5)

I hope this helps,

 

[estyMonsty]

I have thousands of entries and require to match the item with the person and week producing the sum as the result.

Currently the formula I am using (but this is because I have a column with the total monthly sum in it e.g. creating a new column in R):
=IFNA(INDIRECT("'"&{cell referenced with name e.g.B$3}&"'!{column referenced of total monthly hours e.g.R}"&MATCH({cell referenced to match item e.g. $A4},INDIRECT("'"&{cell referenced with name e.g.B$3}&"'!{column:column item referenced e.g.$A:$A }"),0)),0)

I don't want to have to create new columns and was wondering if it was possible to incorporate the sum formula into an indirect match formula or if there was an easier / less manual way.

 

[estyMonsty]

Figured it out. There may be an easier way but:

=IFNA(SUM(INDIRECT("'"&B$3&"'!B"&MATCH($A4,INDIRECT("'"&B$3&"'!$A:$A"),0))INDIRECT("'"&B$3&"'!H"&MATCH($A4,INDIRECT("'"&B$3&"'!$A:$A"),0)))),0)

Phew! Thanks for the direction.