Sumif : Multiple Column Criteria Multiple Sum ranges

[Ammarbokhari]

Hi,
I am tired of searching for an answer to this question,
I have a sheet which looks like this:

In this sheet, as you can see each employee may not work on the same job through out the month and per hour rate for each is also different, so what I need is, in order to find the cost of Labor on each job, I have to search that how many hours a particular employee worked on a specific job (which is done by repetition of {=IF($B6='Daily Man Hours (April)'!C7,'Daily Man Hours (April)'!D7,0)+IF($B6='Daily Man Hours (April)'!F7,'Daily Man Hours (April)'!G7,0)
till the end of the month, and then the formula has to go to the next row and repeat the process (or we can use a new formula for next row if necessary)
but there are more than 100 employees and around 150 different jobs for which each employee working hours are to be separated, this if function is not sufficient.
only way is to give a variable column reference, which is beyond my capability.
Your Response will be highly appreciated
thanking you all in advance.

 

[Sandeep Warrier]

Hi & Welcome to the Board!

It'll be helpful if you could post some sample data.

My signature has a few methods of posting sample data. Please use any one and post some data.

 

[Ammarbokhari]

I dont know why the picture I attached is invisible

but there is a repeat of my question giving more details for your reference

Hi,
I am tired of searching for an answer to this question,
I have a sheet which looks like this:

In this sheet, as you can see each employee may not work on the same job through out the month and per hour rate for each is also different, so what I need is, in order to find the cost of Labor on each job, I have to search that how many hours a particular employee worked on a specific job (which is done by repetition of {=IF($B6='Daily Man Hours (April)'!C7,'Daily Man Hours (April)'!D7,0)+IF($B6='Daily Man Hours (April)'!F7,'Daily Man Hours (April)'!G7,0)
till the end of the month, and then the formula has to go to the next row and repeat the process (or we can use a new formula for next row if necessary)
but there are more than 100 employees and around 150 different jobs for which each employee working hours are to be separated, this if function is not sufficient.
or a macro which can match the value of C7 with a specified job number (which are QTJ-001 to QTJ-150 but one at a time to be used in summary page) if so take the value of D7 (which is next column to column C), then repeat the same process (matching the project name with criteria project name) for F7 (which is 2 columns after column C) and take value of column G7 (which is 2 columns after column D) and add it to D7 value (if C7 verified criteria and F7 also verified) and keep repeating the process till the end of row is arrived. then repeat the process from beginning for Row 8. and so on till the last row is achieved.
only way is to give a variable column reference (First Column (which is C in this case) offset 3, then offset 6, 9, 12 and so on), which is beyond my capability.
I have listed all the search results and my own working for your reference, but all of the above does not solve the problem so far.

Your Response will be highly appreciated
thanking you all in advance.

 

[Sandeep Warrier]

You cannot attach an image if you load it from your hard disc.

Please use one of the methods from my signature or upload a sample file to a site like box.net and post the link here.

 

[Ammarbokhari]

http://www.flickr.com/photos/61836703@N06/5636793245/
here is the link to screen shot of the same file!

 

[Ammarbokhari]

You cannot attach an image if you load it from your hard disc.

Please use one of the methods from my signature or upload a sample file to a site like box.net and post the link here.

http://www.4shared.com/document/jZn8u5Pm/Ammar.html
i have changed the file extension to .txt in order to upload it to site (there was a problem otherwise) please change the extension to xlsm (macro for drop down autofill is in the file)

 

[Haseeb Avarakkan]

Hello Ammar, Try

On Summary C6, copy across & down.

=SUM(IF('Daily Man Hours (April)'!$C$7:$CQ$159=$B6,'Daily Man Hours (April)'!D$7:$CQ$159))

Must hit Control+Shift+Enter, not just Enter.

 

[Ammarbokhari]

Hello Ammar, Try

On Summary C6, copy across & down.

=SUM(IF('Daily Man Hours (April)'!$C$7:$CQ$159=$B6,'Daily Man Hours (April)'!D$7:$CQ$159))

Must hit Control+Shift+Enter, not just Enter.

That is awesome.
It solves my first problem.
But what I further need is job wise summation of each row alone (as each Labour salary is different, and hence unit rate has to be multiplied to get cost impact of labour working hours on job costing).. so If i can get QTJ-001 hours for first labour and multiply it with his man-hour rate and den repeat the process for next row and some on!
Thank you very much for your response. it made my day!

 

[Haseeb Avarakkan]

Try,

Salay C4, copy down & across.

=SUMIF('Daily Man Hours (April)'!$A$5:$CQ$5,C$3,INDEX('Daily Man Hours (April)'!$A$7:$CQ$122,MATCH($B4,'Daily Man Hours (April)'!$A$7:$A$122,0),0))

 

[Ammarbokhari]

Hello Ammar, Try

On Summary C6, copy across & down.

=SUM(IF('Daily Man Hours (April)'!$C$7:$CQ$159=$B6,'Daily Man Hours (April)'!D$7:$CQ$159))

Must hit Control+Shift+Enter, not just Enter.

Try,

Salay C4, copy down & across.

=SUMIF('Daily Man Hours (April)'!$A$5:$CQ$5,C$3,INDEX('Daily Man Hours (April)'!$A$7:$CQ$122,MATCH($B4,'Daily Man Hours (April)'!$A$7:$A$122,0),0))

Hi,
Thank you for your effort it has brought me closest to my answer, and I love the fact that its without using Macro!
Well simple Sumif funciton works in Salary sheet.
But I need a function for labor cost on each job. (Labor Rates are different and if someone else is used on the same job, his cost will be different for the same number of hours). So if I can get number of Hours of a particular labour on a particular job then I can multiply it with per hour cost and then add it to other labour costs on specific job to get total labour cost of that job.
{=SUM(IF('Daily Man Hours (April)'!$C$7:$CQ$7=$B6,'Daily Man Hours (April)'!D$7:$CQ$7))*'Daily Man Hours (April)'!B7}+{=SUM(IF('Daily Man Hours (April)'!$C$8:$CQ$8=$B6,'Daily Man Hours (April)'!D$8:$CQ$8))*'Daily Man Hours (April)'!B8}+ So on for all the rows (159 rows).
(where 'Daily Man Hours (April)'! B contains Per hour rate and above formula is an Array formula as you already told, and I want this formula to be placed on Summary! E6)