Sumproduct

[lars]

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

 

[Asala42]

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

 

[lars]

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

 

[lars]

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


Thanks

 

[Aladin Akyurek]

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

 

[Jay Petrulis]

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

 

[Asala42]

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

 

[lars]

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!!!

 

[lars]

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

 

[Jay Petrulis]

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