# Lookup Sum of Table by Week

Discussion in 'Business Applications' started by estyMonsty, Jul 24, 2018.

Not open for further replies.

Joined:
Mar 22, 2017
Messages:
19
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.

File size:
13 KB
Views:
1
2. ### Chawbacon

Joined:
Jul 9, 2018
Messages:
540
First Name:
Jack
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,

Joined:
Mar 22, 2017
Messages:
19
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.

Joined:
Mar 22, 2017
Messages:
19
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.

As Seen On