SUMPRODUCT with embedded MAX/MIN

Hi Folks -- good evening!

I have a spreadsheet with two tabs. Tab 1: *Job Listing* contains job title, Pay Scale Code and an area to pull in the minimum and maximum hourly wages from Tab 2: *Wage Rates.*

My SUMPRODUCT formula was working until I realized that not all Pay Scales have the same number of pay steps. I need a formula that will allow me to find the minimum or maximum step value and return the correct pay amount for that step.

So instead of (1='Wage Rates'!$B$2:$B$31) below, I need to take the MAX or MIN of the pay steps for that particular pay scale code and return the correct pay value.

=SUMPRODUCT((C2='Wage Rates'!$A$2:$A$31)*(1='Wage Rates'!$B$2:$B$31)*'Wage Rates'!$E$2:$E$31)

Sample Spreadsheet 3.xls

SUMPRODUCT would be my preference but if it isn't a possibility can you let me know why? Still trying to understand this amazing function.

My sample spreadsheet is attached. Hoping there is a way to do this. Thanks!

hi,

Please go through below link may be it will help you if not then write back here please.
http://www.mrexcel.com/forum/excel-q...et-condit.html

Edited: mistakes in original formulas

Try array-entering this in D2 of Min column of Job Listing and copying / filling down.

Formula:

=SUM((MIN(IF('Wage Rates'!$A$2:$A$30=$C2,'Wage Rates'!$B$2:$B$30))='Wage Rates'!$B$2:$B$30)*($C2='Wage Rates'!$A$2:$A$30)*'Wage Rates'!$E$2:$E$30)

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then in E2 do the same with this array formula

Formula:

=SUM((MAX(IF('Wage Rates'!$A$2:$A$30=$C2,'Wage Rates'!$B$2:$B$30))='Wage Rates'!$B$2:$B$30)*($C2='Wage Rates'!$A$2:$A$30)*'Wage Rates'!$E$2:$E$30)

These operate much the same way SUMPRODUCT does but the MIN / MAX functions used here require array (CSE) entry ... which is something array formulas using SUMPRODUCT don't usually require.

I looked for a non-array work around briefly, and so far have found none. This might require using some 'helper' columns.

You are welcome. Thank you for the feedback and rep.

Withdrawn by FR.

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.