You could improve what you have

=IF(OR(C5="insulation",C5="civil",C5="scaffolding" ,C5="scaffolding/painting",C5="painting"),SUMIF('Contractor
1'!$A:$A,'Measures PO'!$B5,'Contractor 1'!$H:$H),
IF(OR(C5="mechanical - structural",C5="mechanical -
pipework",C5="electrical"),SUMIF('Contractor 2'!$A:$A,'Measures
PO'!$B5,'Contractor 2'!$H:$H),"wrong"))

but here is a udf as well

Public Function CustomFunc(ByVal rng As Range, ByVal rng2 As Range)

If rng = "insulation" Or rng = "civil" Or rng = "scaffolding" Or rng =
"scaffolding/painting" Or rng = "painting" Then

CustomFunc = Application.SumIf(Worksheets("Contractor
1").Range("$A$2:$A$242"), rng2, Worksheets("Contractor
1").Range("$H$2:$H$242"))
ElseIf rng = "mechanical - structural" Or rng = "mechanical - pipework"
Or rng = "electrical" Then

CustomFunc = Application.SumIf(Worksheets("Contractor
2").Range("$A$2:$A$190"), rng2, Worksheets("Contractor
2").Range("$H$2:$H$190"))
Else

CustomFunc = "wrong"
End If
End Function




--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
...
I think I'm a bit out of my depth with by knowledge of VBA and I've
been trying to no avail to come up with some code to do encompass the
following array formula:

=IF(OR(cell="text",cell="text",cell="text"),SUMPRO DUCT((range=cell
value)*(range)), IF(OR(cell="text",C5="text"),SUMPRODUCT((range=cel l
value)*(range)),"wrong"))

My actual formula used in the sheet is:

=IF(OR(C5="insulation",C5="civil",C5="scaffolding" ,C5="scaffolding/
painting",C5="painting"),SUMPRODUCT((Contractor 1!$A$2:$A
$242='Measures PO'!$B5)*(Contractor 1!$H$2:$H$242)),
IF(OR(C5="mechanical - structural",C5="mechanical -
pipework",C5="electrical"),SUMPRODUCT((Contractor 2!$A$2:$A
$190='Measures PO'!$B5)*(Contractor 2!$H$2:$H$190)),"wrong"))

What I'd like to do is speed this up with a custom function that so I
can quickly obtain total costs and add more contactors (and in the
case of the text matches disciplines of work) as needed which will be
extremely limited by my array formula. I'm not even sure how possible
it is or if there'd be any other info someone would need me to provide
so they can help me, so any input is welcome.

Thanks

Dave