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