Lookup Sum of Table by Week

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

estyMonsty

Thread Starter
Joined
Mar 22, 2017
Messages
23
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.
 

Attachments

Chawbacon

Jack
Joined
Jul 9, 2018
Messages
627
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

Thread Starter
Joined
Mar 22, 2017
Messages
23
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

Thread Starter
Joined
Mar 22, 2017
Messages
23
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top