Sumproduct
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Sumproduct

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Here is my formula

    =SUMPRODUCT(('327'!$C$6:$C$424=B34)*(MONTH('327'!$F$6:$F$424)>=5)*(MONTH('327'!$F$6:$F$424)<=8)*('327'!$R$6:$R$424))

    my question applies to the last part *('327'!$r$6:$r$424....I need this part to look at two columns instead of just "R" and bring back whichever one is greater.

    If the sum of column M is greater than S than use M or use S if that is greater

    I used this for the last part and it works alone but not with the sumproduct formula
    *((IF(SUM('327'!$R$6:$R$424)>SUM('327'!$S$6:$S$424),SUM('327'!$R$6:$R$424),SUM('327'!$S$6:$S$424)))))

    Any help would be great

    thanks

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    How about:

    =Sumproduct(('327'!$C$6:$C$424=B34)+0,(MONTH('327'!$F$6:$F$424)>=5)+0,(MONTH('327'!$F$6:$F$424)<=8)+0,if(sum('327'!$R$6:$R$424)>sum('327'!$s$6:$s$424),'327'!$R$6:$R$424,'327'!$R$6:$R$424))

    I'm not sure is Excel will go for an if-statement within a sumproduct.

    Good luck
    Adam


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's the trouble I was having as well but thanks I'll give it a shot

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Didn't work, I think the if is throwing it off


    Thanks

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-09 13:45, lars wrote:
    Here is my formula

    =SUMPRODUCT(('327'!$C$6:$C$424=B34)*(MONTH('327'!$F$6:$F$424)>=5)*(MONTH('327'!$F$6:$F$424)<=8)*('327'!$R$6:$R$424))

    my question applies to the last part *('327'!$r$6:$r$424....I need this part to look at two columns instead of just "R" and bring back whichever one is greater.

    If the sum of column M is greater than S than use M or use S if that is greater

    I used this for the last part and it works alone but not with the sumproduct formula
    *((IF(SUM('327'!$R$6:$R$424)>SUM('327'!$S$6:$S$424),SUM('327'!$R$6:$R$424),SUM('327'!$S$6:$S$424)))))

    Any help would be great

    thanks
    Lars,

    • =SUMPRODUCT(('327'!$C$6:$C$424=B34)*(MONTH('327'!$F$6:$F$424)>=5)*(MONTH('327'!$F$6:$F$424)<=8),IF(SUM('327'!$R$6:$R$424)>SUM('327'!$S$6:$S$424),'327'!$R$6:$R$424,'327'!$S$6:$S$424))

      =MAX(SUMPRODUCT(('327'!$C$6:$C$424=B34)*(MONTH('327'!$F$6:$F$424)>=5)*(MONTH('327'!$F$6:$F$424)<=8),'327'!$R$6:$R$424), SUMPRODUCT(('327'!$C$6:$C$424=B34)*(MONTH('327'!$F$6:$F$424)>=5)*(MONTH('327'!$F$6:$F$424)<=8),'327'!$S$6:$S$424))


    are quite different things. Which do you have in mind?

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-04-09 14:25 ]

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Lars,

    SUMPRODUCT won't work like that. Either use another column with =MAX(M,S) or change the formula to an array formula using SUM

    {=SUM(IF('327'!$M$6:$M$424>'327'!$S$6:$S$424,'327'!$M$6:$M$424,'327'!$S$6:$S$424)*('327'!$C$6:$C$424=B34)*(MONTH('327'!$F$6:$F$424)>=5)*(MONTH('327'!$F$6:$F$424)<=8))}

    Must be entered with Ctrl+Shift+Enter. Excel puts the braces {} around the formula, you do not.

    This should work, but please recheck the range references.

    Also, consider naming your ranges to make them easier to work with in formulae like these.

    HTH,
    Jay

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,305
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hey,

    Seems a little clumsy but how about:

    =If(sum('327'!$R$6:$R$424)>sum('327'!$s$6:$s$424),Sumproduct(('327'!$C$6:$C$424=B34)+0,(MONTH('327'!$F$6:$F$424)>=5)+0,(MONTH('327'!$F$6:$F$424)<=8)+0,'327'!$R$6:$R$424,Sumproduct(('327'!$C$6:$C$424=B34)+0,(MONTH('327'!$F$6:$F$424)>=5)+0,(MONTH('327'!$F$6:$F$424)<=8)+0,'327'!$s$6:$s$424)

    Adam

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To be honest whichever one works, I'm just trying to get the last part to find the max of the two column based on the sumproduct formula because I need to use some type of sumif formula

    Let me try what you did thanks!!!

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    California
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    the first part of the frmulas only looks at one coulmn just the last part is looking at two columns to determine which is larger

  10. #10
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    On 2002-04-09 14:31, lars wrote:
    the first part of the frmulas only looks at one coulmn just the last part is looking at two columns to determine which is larger
    Hi Lars,

    Are you choosing the column en masse, depending on the larger sum of the column?

    Col A 2,4,6
    Col B 1,3,100

    Which option do you want?
    1. Col B for all because 104>12?
    2. Col A for the first two items and Col C for the third?
    3. Column A for all because there are 2 items larger than the respective B's to only one B larger than A?

    I'm not sure I am following your request.

    Jay

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com