1 Attachment(s)

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!

Re: SUMPRODUCT with embedded MAX/MIN

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

1 Attachment(s)

Re: SUMPRODUCT with embedded MAX/MIN

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.

Re: SUMPRODUCT with embedded MAX/MIN

Re: SUMPRODUCT with embedded MAX/MIN

Re: SUMPRODUCT with embedded MAX/MIN

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

Re: SUMPRODUCT with embedded MAX/MIN

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

1 Attachment(s)

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.

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. ;)