SUMPRODUCT with embedded MAX/MIN

[bgoodsell]

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!

[udprocks]

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

[mdimran.ahmed30]

Hi,

Please find attached excel file, i have used MIN & MAX function instead of SUMPRODUCT function i hope will help you.

if you Satisfy with my answer so please Add reputation. you can click your right side * button.

Imran.

[FlameRetired]

Edited: mistakes in original formulas

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

[FlameRetired]

Withdrawn by FR.

[bgoodsell]

Thank you -- another awesome formula! Thanks for explaining things so clearly; it really helps me learn.

[FlameRetired]

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

[FlameRetired]

An afterthought.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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.

[bgoodsell]

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.