I was thinking along the same lien, except my code doesn't work :( I think it
faults at the first line where I am attempting to set the range (a range in a
different worksheet).

Can you please help me? Thanks a mil.

Function getEmployeeOnSkill(officeNo As Integer, state As Integer,
skillLevel As String, empRange As Range) As Variant
Dim rngeOffice As Range, rngeState As Range, rngeSkill As Range


Set rngeOffice = Sheets("Staffing All Sites").Range("P1:P200")
Set rngeState = Sheets("Staffing All Sites").Range("Q1:Q200")
Set rngeSkill = Sheets("Staffing All Sites").Range("D1:D200")

getEmployeeOnSkill = Evaluate("SUMPRODUCT((" & rngeOffice & "=" &
officeNo & ")*(" & rngeState & "=" & state _
& ")*(" & rngeSkill & "=" & skillLevel & ")*" &
empRange & ")")
End Function


The aim of the function is to return sum in the empRange, given matches in
the other three columns... The three columns stay fixed, empRange moves
around a bit.....

Cheers

"Gary''s Student" wrote:

Function getperson(s1 As String, s2 As String) As Integer
dq = Chr(34)
p1 = "=sumproduct((D1:D10=" & dq
p2 = dq & ")*(E1:E10=" & dq
p3 = dq & ")*(F1:F10))"
func = p1 & s1 & p2 & s2 & p3
getperson = Evaluate(func)
End Function


Use as:

=getperson("June","Mark")
--
Gary''s Student - gsnu200740


"kMan" wrote:

Hello all,

Appreciate your help.

Basically, I have a looong equation in an excel cell, mainly utilising
Sumproduct function. I.e. (simplified)

Cell A4:
= sumproduct((D1:D10="June")*(E1:E10="Mark")*(F1:F10 ))

Problem is that function above is repeated for multiple entities (i.e. John,
Lisa, Nick.... inadidition to Mark) and it gets rather messy.
I'm hoping to define a VBA function, such as:
getPerson(D1:D10, June, E1:E10, Mark) or getPerson(June, Mark)

to make it easy for the person auditing the worksheet (and the second
variant so that I don't have to pass the constant references everytime)....

Is this possible?