Sum values from table based on list returned from array

I have an array formula that returns a list of areas scheduled for service for a given week number. I need a formula to evaluate that list against a table that provides man-hours per area, acres per area, etc and sums these values for that week. Currently I am using several large arrays that do this already but I need a more efficient method. I have tried combinations of SUMIF, INDEX/MATCH and OFFSET but the formulas will only evaluate the first area on the list and not the entire array for that week. I've attached a simplified spreadsheet for reference, thanks:

Originally Posted by Glenn Kennedy

Some indication of what results you expect and where you expect them to be would be helpful. All I can see is a mass of numbers.

The results woild appear in rows 40 and 41. I updated the example sheet in my thread reply to show expected values. Thanks.

VBA UDF:

Please Login or Register  to view this content.

=get_total(B$2:B$39,1) .. Manhours


=get_total(B$2:B$39,2) ....Acres

Thanks, is there a solution that does not use VBA?

I am sure there is but I have been unable to devise one!!