SUMPRODUCT(MAX( Question

[brans1982]

Hi,

I'm having an issue with a Sumproduct using the max function.

This formula works fine...

=SUMPRODUCT(MAX((Data!$A$2:$A$48=$C$6)*(Data!$G$2:$G$48=$D49)*(Data!$L$2:$L$48)))

And so does the below until I copy it past Row 48...

=SUMPRODUCT(MAX((MONTH(Data!$A$2:$A$48)=9)*(Data!$G$2:$G$48=$D49)*(Data!$L$2:$L$48)))

At the minue, my data on the Data! sheet finishes on Row 48. It's got something to do with the fact I've inserted the MONTH Function into the formula.

Anybody any ideas? Thanks!

 

[Robert Mika]

What kind of an error are you getting?

 

[brans1982]

Hey,

I was getting a value error. Figured it out, I enetered it as an array formula and it worked fine. Then because of that, I could get rid of the SUMPRODUCT and enter it as....

{=MAX((MONTH(Data!$A$2:$A$48)=9)*(Data!$G$2:$G$48=$D49)*(Data!$L$2:$L$48))}

 

[Yahya]

Same formula with using IF
as i know using if with this CSE formula is better than without iF
=MAX(IF(MONTH(Data!$A$2:$A$48)=9,IF(Data!$G$2:$G$48=D49,Data!$L$2:$L$48)))

 

[Aladin Akyurek]

Wrapping a formula with the SumProduct function in order to avoid applying control+shift+enter does not look as a 'good practice'.

Also, as suggested by Yahya, using IFs for filtering in lieu of pairwise multiplications is a better choice. I believe moreover that adding a year test would be correct unless the year is not important at all...

Control+shift+enter, not just enter:

=MAX(IF(Data!$A$2:$A$48-DAY(Data!$A$2:$A$48)+1=D48,IF(Data!$G$2:$G$48=D49,Data!$L$2:$L$48)))

where D48 houses a date criterion like:

1-Sep-2011

 

[hatstand]

Or you could:

=SUMPRODUCT(MAX((A1:A10=E2)*(B1:B10)))+SUMPRODUCT(MAX((A1:A10=E2)*(C1:C10)))

E2 is where the letter is your looking to sum
A1:A10 being where your letters.
B1:B10 first figures
C1:C10 second figures

 

[Aladin Akyurek]

Or you could:

=SUMPRODUCT(MAX((A1:A10=E2)*(B1:B10)))+SUMPRODUCT(MAX((A1:A10=E2)*(C1:C10)))

E2 is where the letter is your looking to sum
A1:A10 being where your letters.
B1:B10 first figures
C1:C10 second figures

Why? The thread in fact revolves around the fact that SumProduct should not be used as a substitute for the control+shift+enter key combination.

 

[hatstand]

My reply was meant for another question.