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

[jeremyb]

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

 

[Gerald Higgins]

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]

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:

=SUMPRODUCT(--(A1:A10=”fruit”),--(B1:B10=”apple”),C1:C10)

Hope that helps you!