Need Help with summation and reflecting the sum in new sheet

[vmtaurus]

Hello,

I have two sheets within the attached excel.

Sheet 1 - Gives me details consultant wise, scheduling in a project, week wise, day wise for 13 weeks.

Sheet 2 - Has the same consultant names, but with different columns.

My problem: I want to sum up the hours per day into per week (for the 13 weeks) from Sheet 2 and want it to reflect against the corresponding consultant's name in Sheet 2. .

Any help would be much appreciated. I need to do this on a weekly basis, what is the best way to do this? Formula?Macro?

The number of rows will vary weekly with additions and deletions.

Thank you so much in advance!
VM

[haripopuri]

Paste this formula in G2, copy till S9.

Please Login or Register  to view this content.

[haripopuri]

In future, remember to delete all sensitive data from the sample file. Can be easily misused.

[nathansav]

I changed your dates in sheet 1 to be dates and then used prev date +7 for the future dates

then used

Please Login or Register  to view this content.

[vmtaurus]

Thank you Hari. The data is just gibberish and not actuals.

I tried your formula, and it works perfectly. Only when I copy onto my original data, it does not return any values. I changed the cell values and data arrays accordingly, but it still didnt work. Any tips you can help with?

Much appreciate your help.

Thank you

[haripopuri]

I was taking about the email IDs, I am sure that they are actuals

This is how the formula works, see if you can tweak it to know where it is going wrong.
From A3 to DR12 (this is where your daily data lies, you have to adjust as per your new data range) - Pick a row where resource name is matching (Row number is returned by comparing resource name in Sheet2 D column with the list of resources in Sheet1, B column.
Then Pick a column where date as in Sheet2 2nd row Column G and onwards. Check matching column in 3rd row Sheet1.

I have hard coded the date format that you have in Sheet1 as 06-Jun-2014 Total . Only tricky part here is that the date format in Sheet1 should always be in the same format. Apart from this, this formula should work for you.

If you still have problems, upload the book you are working on. Remember to adjust the references as per your data.

[haripopuri]

Also try formula evaluation step by step, you might catch where it is going wrong in your case.

[vmtaurus]

Thank You Hari

So i tried all your suggestions but it still does not return a value. When I evaluate, it tells me that there is a missing #N/A value, which I can't figure out where. I've attached my worksheet. Would be great if you can have a look and tell me where I am going wrong. Much, much appreciate your help!

[nathansav]

Have you tried my solution?

[vmtaurus]

Hi Nathan,

I tried it, but I find it too complicated for me. I'm very code and formula illiterate and hence found it easier to work with Hari's approach.
I was not sure how to use the formula you mentioned.

Thank you for checking in!

[haripopuri]

Please Login or Register  to view this content.

[vmtaurus]

Thank you!!! Works now!

[nathansav]

No worries, they were pretty much the same solution :o)

[haripopuri]

Just noticed that above formula is not working for 4-Jul piece. Below should work if you also face the same problem

Please Login or Register  to view this content.

[vmtaurus]

Thank you much!!! It was giving me the error, but now its perfect.

You are awesome!

[haripopuri]

I am glad that I could help. If the problem is resolved, please update the thread as SOLVED. You may wish to add a point to my reputation.

[haripopuri]

Update the thread as solved. You may wish to add to my reputation.