+ Reply to Thread
Results 1 to 9 of 9

Variant of Max Sumif Formula?

  1. 06-10-2014, 09:34 PM #1
    ynwa77
    ynwa77 is offline
    Registered User
    Join Date
    11-07-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Variant of Max Sumif Formula?

    Hi,

    I've got a spreadsheet with information on resource allocation. What i want to do is to have a formula that calculates which project each person has spent the most time on in a given month, as well as telling me the percentage of time they have spent on it. The formula should be dynamic and driven off a data validation list of the months (as depicted in the first sheet), so that the it gives the information depending on what month is selected. All the resource allocation information is in the second sheet.

    I've tried a multitude of different formula combinations with arrays, max, sumif etc. and can't seem to get it to work.

    Any help and advice is greatly appreciated!

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-10-2014, 10:42 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Variant of Max Sumif Formula?

    Hi and welcome to the forum

    Im not sure what you mean by "Focus" and "% allocation", so I based this off columns H:S

    Try this array formula in G23, copied down...
    =MAX(IF(Forecast!$A$4:$A$287='Resource Utilization'!$F23,OFFSET(Forecast!$H$3,1,MATCH($H$11,Forecast!$H$3:$S$3,0)-1,COUNT(Forecast!$O$4:$O$287)*1),0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 06-10-2014, 10:57 PM #3
    ynwa77
    ynwa77 is offline
    Registered User
    Join Date
    11-07-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Variant of Max Sumif Formula?

    Thanks FDibbins.

    That's solved my problem for the "% allocation" formula. The "focus" formula will ideally return the project which corresponds to the max value, ie. the text in column F.
    Reply With QuoteRegister To Reply
  4. 06-10-2014, 11:39 PM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Variant of Max Sumif Formula?

    OK that makes it easier. Use this regular formula, copied down...
    =INDEX(Forecast!$F$4:$F$287,MATCH('Resource Utilization'!H23,OFFSET(Forecast!$H$3,1,MATCH($H$11,Forecast!$H$3:$S$3,0)-1,COUNT(Forecast!$O$4:$O$287)*1),0))
    Reply With QuoteRegister To Reply
  5. 06-11-2014, 01:00 AM #5
    ynwa77
    ynwa77 is offline
    Registered User
    Join Date
    11-07-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Variant of Max Sumif Formula?

    Thanks a lot mate!

    One last question..

    Cao, Daniel
    Finlayson, Adam
    Megennis, Janine
    Novilla III, Francisco
    Robinson, Michael
    Slade, Michael
    Wang, Scott
    Xuereb, Matt
    Yanamala, Kalpana
    Zaloumis, Tereska

    For some reason the formula errors out on these guys and i can't seem to figure out why..

    Could you shed any light on this?

    Thanks a bunch!
    Reply With QuoteRegister To Reply
  6. 06-11-2014, 01:06 PM #6
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Variant of Max Sumif Formula?

    I ahve looked at this and cannot (yet) figure out the error, so I have asked the other experts for help
    Reply With QuoteRegister To Reply
  7. 06-11-2014, 02:00 PM #7
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Variant of Max Sumif Formula?

    looks like the count in the offset is doing it as the range is too small
    perhaps
    ,COUNTA(Forecast!$A$4:$A$287) instead or just hard code it with the number of rows
    Last edited by martindwilson; 06-11-2014 at 08:04 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  8. 06-11-2014, 02:20 PM #8
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Variant of Max Sumif Formula?

    Awesome catch Martin, thanks

    Changed the column ref, and changed to COUNTA() I Have a feeling the range in Forcast will increase, thats why I made it dynamic...

    ynwa77 change to this ARRAY...
    =MAX(IF(Forecast!$A$4:$A$287='Resource Utilization'!$F36,OFFSET(Forecast!$H$3,1,MATCH($H$11,Forecast!$H$3:$S$3,0)-1,COUNTA(Forecast!$F$4:$F$287)),0))
    Reply With QuoteRegister To Reply
  9. 06-11-2014, 07:39 PM #9
    ynwa77
    ynwa77 is offline
    Registered User
    Join Date
    11-07-2013
    Location
    Wellington, New Zealand
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Variant of Max Sumif Formula?

    Sorry, but i have one last request

    I just realised that the second formula, that determines the project most worked on, doesn't appear to contingent on the person. It seems to pick up the project that is associated with the first instance of whatever value the first formula finds.

    For example, when i select May, for Brooky, Steve i would expect the formula to return "MTS/TR", however the formula returns "LAPS BAU Enhancement" because the first instance of 100% appears with that project, but for someone else.

    Any help on fixing that would be most welcome!

    Thanks so much.
    Last edited by ynwa77; 06-11-2014 at 11:15 PM. Reason: Further Clarifiation
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. SUMIF or SUMIF's formula with multiple criteria
    By jackiemariee in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-30-2013, 06:19 PM
  2. Top 3 (MAX function variant)
    By Shane O in forum Excel General
    Replies: 2
    Last Post: 07-19-2010, 02:10 PM
  3. Creating a variant of the SUMIF function
    By nik2shah in forum Excel General
    Replies: 1
    Last Post: 04-29-2008, 01:07 PM
  4. variant to long?
    By James Cornthwaite in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2006, 11:00 AM
  5. Variant
    By owl527 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-14-2005, 05:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1