Vlookup sum data from a table for each occurance of an item

jeremyb

New Member
Joined
Aug 11, 2005
Messages
13
Could someone help me please

I am extracting data from a d/b, and have 4 columns of data:

Empno
Date
Timecode
Duration

On a daily basis there are multiple entries for each employee, for date, with different timecodes and differing durations.

I have built a privot table which produces a table by employee (rows) for each timecode (coulmns) summing the durations.

I then use vlookup in another spreadsheet to extract the relevant data to compute certain rates etc.
Timecodes are a fixed number, but may not all be used in a month. This means that the Pivot table will be dynamic, new columns being added as timecodes are used during the month e.g. Day 1 Timecodes 1,2,3,10,11,12 maybe used. On Day 2 Timecodes 1,2,3,6,7,8,10,11,12 may also be used and so on.

This doesn't help the end spreadsheet as the VLookups will have to be continually updated to keep pace with the changes in use of Timecodes.
I hope this makes sense so far.

What I'd like to do is have a Vlookup that looks at the original Table, and will sum all Durations for a specific Timecode for each Empno.
I don't know how to develop the formula to get this to work.

Can anyone advise what this should be.

Many thanks


Jeremy
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,258
Hi Jeremy

This might not be the best way, but I think it would work, and it would be quite easy to do and hopefully reliable.

How about creating a dummy employee record, which has entries for all possible time codes ? This would then force the pivot table to display them all, even if some of them were never used. You'd need to make sure that there was no other meaningful data for the dummy record.

Also, if you were doing employee counts or similar functions, you'd have to take the dummy record into account.
 

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
Sounds like you need to use SUMPRODUCT. Hard to see exactly what you need but as an example if you wanted to sum everything in column C where column A equals “fruit” and column B equals “apple”, you would use something like:
Code:
=SUMPRODUCT(--(A1:A10=”fruit”),--(B1:B10=”apple”),C1:C10)

Hope that helps you!
 

Forum statistics

Threads
1,181,730
Messages
5,931,701
Members
436,799
Latest member
BasOo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top