Multisumif function!

[Ammarbokhari]

Hi everyone;
can anyone explain what this function is meant to do? and what are the limitations which need to be kept in mind for using it?

Function MultiSumif(Looky As Range, Job As Range, Offset 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
Hrs = rFoundCell.Offset(0, Offset)
Rate = rFoundCell.Offset(0, 4 - rFoundCell.Column)

TotalCost = TotalCost + Hrs * OTRate * Rate

Next lCount

MultiSumif = TotalCost

End Function

I am not familiar with programming and someone on this forum made this function to help me.
What this function is supposed to do is; search each row based on a criteria and repeat the procedure till the end of row (and add values in cells at given offsets to the cell found and then multiply the sum of these values to a value given in column 4)
But its not working perfectly and giving an over all difference of 2200.


(first cell found by criteria, offset 1, pick value then restart search from next cell in same row where value matched, again cell found by criteria, pick value from cell at an offset of 1 and add to previous value. Repeat the process till the end of row and then multiply with the value in column 4 of same row.
Restart the process from next row and search by criteria and add values at an offset of 1 to the cell where criteria matched, and multiply with value in column 4 of same row. Process is repeated for the complete range selected)
I need criteria to be changeable, also I require offset value to be changeable (but for one particular formula offset will remain same). I also want to be able to multiply the end values with a factor (mentioned as OTRate in code, which is also variable but will remain same for one particular formula). In short the function is perfectly built for my requirement but is not giving the precise result and I need your help to find out what I am doing wrong.

I hope I explained my question.
Thank you for your patience and time

 

[jasonb75]

Based on the screen image in your other thread, (can't download the file) I would suggest deleting the UDF and using native functions instead.

Something like

=SUMPRODUCT(--(C9:CL9="Job"),(SUBSTITUTE(D9:CM9,"Job",0)*B9)+(SUBSTITUTE(E9:CN9,"Job",0)*(1.5*B9)))

or

=SUM(SUMIF($C9:$CL9,"Job",$D9:$CM9)*$B9,SUMIF($C9:$CL9,"Job",$E9:$CN9)*($B9*1.5))

In both, "Job" refers to the same Job criteria as used in the UDF.