Complex VLOOKUP or SUM

Hi Guys,


I have an issue regarding summing certain peoples hours in a spreadsheet.


So I have tasks by row: arbitrarily say 14 tasks in Column A (A1:A14) .

In Column B are the quantities of task (B1:B14).

In column C there are people (C1:C14)
In column D there are the peoples hours (D1:D14)

In column E there are people (E1:E14)
In column F there are the peoples hours (F1:F14)

and so on to

In column S there are people (S1:S14)
In column T there are the peoples hours (T1:T14)

I really want to know how many hours Sandra did for the 14 tasks when the task quantities differ!



I've tried a sumif by pair of columns (CD,EF..........) but i cant account for the quantities by row.
I've tried VLOOKUP but it only works for 1 instance of sandra when there are actually many.

Looking forward to some help on this one.


Thank-you very much in advance

Ben

Could you post some sample data in a workbook?

BSB

I don't know how to add a workbook =(

Follow the guide below.

BSB


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks,

It should be attached below.

Please see the total hours that i'm trying to achieve.

Regards

Ben Illingworth

So what are your expected results?
I'm unsure how the "Quantity" column comes into this.

Guessing that the below formula in cell G19 and copied down is not quite what you're looking for:

Formula:

Please Login or Register  to view this content.

BSB

I've tried that one,

With regards to quantity in column 8 there was the task done 8 times so the hours are essentially 8 times bigger for row 5 they are 8 times bigger

Ben Illingworth

Sorry that should say 4 times bigger for row 5 by the way!

I'm drawing a bit of a mental blank on this one. It's been a long day at the office!

In the attached is one method of calculating what I think you need. It uses a helper range (the yellow cells) that you can hide away for the sake of neatness.

I'm sure a clever soul will come along with an "in cell" solution for you soon.
Well either than or the second I turn the computer off the answer will land in my brain!

BSB

If there was another column added before all the others which had ticks in it if the task was required and was empty if they weren't could this be added to the formula?

Thanks a bunch newdoverman, Legend!