+ Reply to Thread
Results 1 to 9 of 9

SUMPRODUCT with embedded MAX/MIN

  1. #1
    Registered User
    Join Date
    12-29-2015
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2010
    Posts
    10

    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!
    Last edited by bgoodsell; 01-07-2016 at 02:21 PM.

  2. #2
    Forum Contributor
    Join Date
    10-29-2014
    Location
    udaipur, rajasthan
    MS-Off Ver
    2010
    Posts
    306

    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
    If answer helped you say Thanks by Add Reputation

  3. #3
    Registered User
    Join Date
    12-07-2015
    Location
    delhi
    MS-Off Ver
    2010
    Posts
    80

    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.
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,580

    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: copy to clipboard
    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
    Formula: copy to clipboard
    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.
    Last edited by FlameRetired; 01-07-2016 at 02:06 AM.
    Dave

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,580

    Re: SUMPRODUCT with embedded MAX/MIN

    Withdrawn by FR.

  6. #6
    Registered User
    Join Date
    12-29-2015
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: SUMPRODUCT with embedded MAX/MIN

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

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,580

    Re: SUMPRODUCT with embedded MAX/MIN

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

  8. #8
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,580

    Re: SUMPRODUCT with embedded MAX/MIN

    An afterthought.

    Find this formula in ‘Job Listing’ G2:H7 of the attached.
    Formula: copy to clipboard
    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.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Formula: copy to clipboard
    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.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-29-2015
    Location
    Sunnyvale, CA
    MS-Off Ver
    Excel 2010
    Posts
    10

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Count SUMPRODUCT Members / Average of SUMPRODUCT
    By Shingaru in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 03:59 PM
  2. [SOLVED] COUNTIF embedded in a SUMPRODUCT formula
    By macrorookie in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2014, 09:02 AM
  3. Replies: 1
    Last Post: 02-21-2013, 12:11 PM
  4. Replies: 1
    Last Post: 05-19-2012, 02:54 AM
  5. Replies: 5
    Last Post: 04-20-2012, 08:54 AM
  6. Using a SumProduct Count to find a SumProduct Total?
    By XL021710 in forum Excel General
    Replies: 3
    Last Post: 02-18-2010, 08:31 AM
  7. LEFT embedded in SUMPRODUCT
    By marika1981 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-20-2005, 03:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1