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.

 

[TonyMJ]

Have you tried SUMPRODUCT?

If I read your image correctly you have days across the columns and each day is split into Job No, Basic and OT. The rate to apply appears in column B & data will fill down to row 159. B6 (on Summary sheet) contains the job no you are trying to sum?

So for the data in columns C to E :
=SUMPRODUCT(('Daily Man Hours (April)'!C$8:C$159=Summary!$B6)*('Daily Man Hours (April)'!D$8:D$159)*('Daily Man Hours (April)'!E$8:E$159*1.5)*('Daily Man Hours (April)'!$B$8:$B$159))

I have presumed OT = Overtime and have a applied a factor of 1.5 but of course this could anything or another cell or even vary by row.

Personally I would put this in row 1 of the 'Daily Man Hours' sheet say in C1 for the above then copy to F1/I1/L1 etc... and then sum these on the summary. Neater than a monster formula (& I think quicker to calc).

 

[TonyMJ]

Quick correction to my formula (shouldn't write formulas without Excel in front of me!!!)

=SUMPRODUCT(('Daily Man Hours (April)'!C$8:C$159=Summary!$B6)*('Daily Man Hours (April)'!D$8:D$159)*('Daily Man Hours (April)'!$B$8:$B$159))+SUMPRODUCT(('Daily Man Hours (April)'!C$8:C$159=Summary!$B6)*('Daily Man Hours (April)'!E$8:E$159*1.5)*('Daily Man Hours (April)'!$B$8:$B$159))

Need to split Basic & OT otherwise you will multiply one by the other which wouldn't work

 

[Ammarbokhari]

Have you tried SUMPRODUCT?

If I read your image correctly you have days across the columns and each day is split into Job No, Basic and OT. The rate to apply appears in column B & data will fill down to row 159. B6 (on Summary sheet) contains the job no you are trying to sum?

So for the data in columns C to E :
=SUMPRODUCT(('Daily Man Hours (April)'!C$8:C$159=Summary!$B6)*('Daily Man Hours (April)'!D$8:D$159)*('Daily Man Hours (April)'!E$8:E$159*1.5)*('Daily Man Hours (April)'!$B$8:$B$159))

I have presumed OT = Overtime and have a applied a factor of 1.5 but of course this could anything or another cell or even vary by row.

Personally I would put this in row 1 of the 'Daily Man Hours' sheet say in C1 for the above then copy to F1/I1/L1 etc... and then sum these on the summary. Neater than a monster formula (& I think quicker to calc).

Hi TonyMJ,
Yes OT is Overtime and its 1.5 time Basic.
What this formula is not doing correctly is, if there are no OT hours it will give a zero value.
Also what I need is a formula which will do what your formula does (when there are values in both OT and Basic Hrs) for all the columns at a time instead of doing it for three columns (C to E) at a time, as I have to use it for all the 300 + jobs.
I hope, I could state it clearly enough.

Thank you for your help.
Best Regards

 

[TonyMJ]

Further to your PM (thought I would post here in case someone else was following) it is a bit messy having 31 SUMPRODUCT formulas. Also they can be quite resource/calc hungry.

Your only other option (I think) would be to write a UDF but I'm not sure how comfortable you are with VBA?

My first stab would look like:

Function MultiSumIf(Looky As Range, Job As Range, BasicOffset As Integer, OTOffset As Integer, OTRate As Double)

Application.Volatile

Dim lCount As Long, rFoundCell As Range, BasicHrs, OTHrs, Rate, TotalCost

Set rFoundCell = Looky.Columns(1).Cells(1, 1)

On Error Resume Next

For lCount = 1 To WorksheetFunction.CountIf(Looky, Job.Value)

    Set rFoundCell = Looky.Find(Job.Value, rFoundCell, xlValues, xlPart, xlByRows, xlNext, False)
    FoundAdd = rFoundCell.Address
    BasicHrs = rFoundCell.Offset(0, BasicOffset)
    OTHrs = rFoundCell.Offset(0, OTOffset)
    Rate = rFoundCell.Offset(0, 2 - rFoundCell.Column)
    
    TotalCost = TotalCost + BasicHrs * Rate + OTHrs * OTRate * Rate
    
Next lCount

MultiSumIf = TotalCost

End Function

Then the formula entered would be something like =multisumif(DailySheet!C3:K7,Summary!A2,1,2,1.5)
where DailySheet!C3:K7 is the entire data range,
Summary!A2 is the Job Name you want to sum
1 is the column offset for basic (assuming that it is always to the right of the job name)
2 is the column offset for OT (same assumption as above)
and 1.5 is the OT rate to apply
It also assumes that the rate is in column B which you could vary if needed

I haven't fully debugged or tested

 

[Ammarbokhari]

After putting the above mentioned VB code in Module and then using the formula
=MultiSumIf('Daily Man Hour-Apr'!D10:CR162,'Job Wise Man Hr'!B6,1,2,1.5)
I get 0 as an answer while it should be 570.
here ('Daily Man Hour-Apr'!D10:CR162 is the range of values, Job Wise Man Hr'!B6 is the job name, and 1,2,1.5 is same as you mentioned above).
And there are no error messages either.

Thank you for your support and effort
Best Regards,

 

[TonyMJ]

Without seeing your data it is difficult to debug - works well with my sample data but that doesn't help you. Is it the same as the image posted earlier?

Does column B contain the individual £/$ rates relating to the row?

 

[Ammarbokhari]

Posting the link to the latest file that I have updated.
Again I have changed the extension to .txt for the same reasons as above mentioned.

http://www.4shared.com/document/TF6zpkrB/Salary_Sheet_Format.html

Thank you for your time.

 

[TonyMJ]

Ah looks like a simple answer - you have your hourly rate in column C rather than column B.

In the code change the line
Rate = rFoundCell.Offset(0, 2 - rFoundCell.Column)
to
Rate = rFoundCell.Offset(0, 3 - rFoundCell.Column)

Hopefully that will work

 

[Ammarbokhari]

Mr. TonyMJ
A million thanks to you for your help
It worked wonders!
Its completely functional now
And is there a way by which I can give you 500+ Thumbs Up.
Thank you very much.

 

[Ammarbokhari]

How do I tell VB that when a Combo Box is inactive, hide it.
or when any cell other than combo box is active, Hide combo box.

With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With

this is the code I have, but when its filled, it takes some value to be inserted in another cell to hide it.

can you help me on this also?