# SUMPRODUCT with embedded MAX/MIN

• 01-07-2016, 12:07 AM
bgoodsell
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)

Attachment 438855

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!
• 01-07-2016, 12:27 AM
udprocks
Re: SUMPRODUCT with embedded MAX/MIN
hi,

http://www.mrexcel.com/forum/excel-q...et-condit.html
• 01-07-2016, 12:36 AM
mdimran.ahmed30
Re: SUMPRODUCT with embedded MAX/MIN
Hi,

Imran.
• 01-07-2016, 12:43 AM
FlameRetired
Re: SUMPRODUCT with embedded MAX/MIN
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.
• 01-07-2016, 01:59 AM
FlameRetired
Re: SUMPRODUCT with embedded MAX/MIN
Withdrawn by FR.
• 01-07-2016, 02:19 PM
bgoodsell
Re: SUMPRODUCT with embedded MAX/MIN
Thank you -- another awesome formula! Thanks for explaining things so clearly; it really helps me learn. :)
• 01-07-2016, 03:53 PM
FlameRetired
Re: SUMPRODUCT with embedded MAX/MIN
You are welcome. Thank you for the feedback and rep. :)
• 01-07-2016, 04:12 PM
FlameRetired
Re: 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.
• 01-07-2016, 08:20 PM
bgoodsell
Re: SUMPRODUCT with embedded MAX/MIN
Thanks for the alternate formula, FlameRetired! For my purposes this time, I think your first array formula makes sense (dealing with hundreds of job codes) but it's nice to know I have this work around for situations where it may slow my (already slow) computing powers. ;)