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]

First questions is the combobox on the sheet or in a userform?

 

[Ammarbokhari]

=EMBED("Forms.ComboBox.1","")
this where the combo box is!
it appears on my sheet.
and the code I need to change is already in my sheet that I shared with you.
Awaiting another positive response

 

[TonyMJ]

Excel can only do something based on events - something to trigger it into action, press something, move somewhere etc...

So to hide the ComboBox when the user selects somewhere else on the sheet you could use the SelectionChange event for that sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1.Visible = False
End Sub

You might want to repost on a new thread to get other involved as it might only be me looking now!!

 

[Ammarbokhari]

Excel can only do something based on events - something to trigger it into action, press something, move somewhere etc...

So to hide the ComboBox when the user selects somewhere else on the sheet you could use the SelectionChange event for that sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ComboBox1.Visible = False
End Sub

You might want to repost on a new thread to get other involved as it might only be me looking now!!

Okay thank you.
This code did not work as I already have a this code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
and it makes your code ambiguous.
As I know nothing about the VB language, I don't know how to work across it.
I think, I will repost the question in a new thread on Sunday!
Thank you for time
and have a great weekend

 

[Ammarbokhari]

Hi,
The code provided by Tony MJ earlier is totally functional with the exception of xlPart needs to be replaced by xlWhole
I had been searching ever since I received the code and used it on my whole data, there was a small value error (as I had three jobs named QJI-045, QJI-045A and QJI-045B)... Just today I was able to solve the riddle (as I have mentioned, Programming is not my thing. And there are around 200 different jobs, so trouble shooting had to be done by looking into each job one by one).

Thank you once again Tony MJ.

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, xlWhole, 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

 

[Ammarbokhari]

Continued from the above message; "TonyMJ"

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
Rate = rFoundCell.Offset(0, 2 - rFoundCell.Column) , which can be changed to C, D, E etc by changing value from from 2 to 3,4,5,6 etc respectively.