SUMPRODUCT with embedded MAX/MIN

An afterthought.

Find this formula in ‘Job Listing’ G2:H7 of the attached.

Formula:

=SUMIFS('Wage Rates'!$E$2:$E$30,'Wage Rates'!$A$2:$A$30,'Job Listing'!$C2,'Wage Rates'!$B$2:$B$30,HLOOKUP($C2,'Wage Rates'!$I$1:$O$3,COLUMNS($A:A)+1,0))

It is non-array, and depends upon a small helper range in ‘Wage Rates’. With column headings of the unique Pay Scale Codes these array formulas go into I2 and I3 copied across.

Formula:

=MIN(IF(I$1=$A$2:$A$30,$B$2:$B$30))

Formula:

=MAX(IF(I$1=$A$2:$A$30,$B$2:$B$30))

These identify the min and max Step Codes for each of the Pay Scale Codes. While they are array formulas they’re impact on performance is light, and they save having to recalculate them in an array formula like posted earlier. If you are not aware of it array formulas are resource hungry. If you have many of them to do in ‘Job Listing’ they can slow your workbook down. As powerful as SUMPRODUCT like formulas are SUMIF(S) is more efficient.

Hope this helps.