Looking for help with VLookup (I think)

[katashatzu]

Hello, Im working on a project to record how long Ive been working on jobs in different applications. I have my main table which has the job number, start and end date and time, how many minutes and hours worked on each job, and which application I was using. Ive got a second table (this is where I need help) where I would like to total the time spent working in each application. If you look at the image Ive attached, I need the values in L4, L5, L6 etc.

This is there Im thinking I need a lookup (not my strong suit in excel)

For cell L4, I need to lookup the values in I6, I7, I8 etc, and see if theres an A there. If there is, the application used was access and Ill need to total the time up (value from the Hours column, so H6, H7 etc) and put that totaled value in cell L4.

Does that make sense? I dont really know how else to describe it. Hopefully someone can see what Im looking for. I appreciate any input!

Thanks in advance! -Dennis

ExcelHelp.jpg

[dilipandey]

Hi Katashatzu,


Welcome to the forum.

I am not able to view / download you attachment, please correct that. In case of any issues, connect with site admins. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Down Under]

From your JPEG file I think this would work if put into L4 and copy down:

=SUMIF($I$6:$I$150,MID(K4,LEN(K4)-1,1),$H$6:$H$150)

Note though that this will only work if your application tools value always ends with a single character value inside brackets. So if you changed 'Word (W)' to 'Word (Wd)' or 'Word - W' it would not work. Somebody will probably have a more efficient solution but that's the only one I can think of.

[katashatzu]

Sorry, let me try to attach the file again.

JobCalculations.xlsx

[Down Under]

I've put the formula I suggested into your workbook. As I said though, any change to the structure of your Applications value will mean it stops working.

[Fotis1991]

Hi

Me too, but as i understant from the picture, then give a try to this formula, in La.(Same for the others....)

=SUMPRODUCT((I6:I27="a")*(H6:H27))

[galvinpaddy]

Hi,

Please try the attached, i have added in a similar formula to calculate the totals for each week, which will change according to the info you enter.
galvinpaddy