Max Value, Sum Product, Between Date Range

[Chris26]

I have used the following formula to return the max value for ID1, ID2, ID3
etc for the whole data set.

=SUMPRODUCT(MAX((X2:X5000=A2)*(Y2:Y5000))

Where
Col A = ID1, ID2, ID3 etc in my new table.

Imported data (X,Y,Z)
Col X = ID1, ID2, ID3 etc (multiple occurances/values for each ID)
Col Y = Value
Col Z = Date (Format dd/mm/yyyy)

I would like to be able to extract the MAX value for ID1, ID2 etc between
date periods i.e. 1/10/1995 to 1/12/1995 but am unsure how to do this.

Any help appreciated
Many Thanks
Chris

 

[Jacob Skaria]

The below formula will pick the max value from ColB ....for ID1 (cell F1)
between the dates mentioned in D1 and E1

=MAX(IF((A1:A100=F1)*(C1:C100>=D1)*(C1:C100<=E1),B1:B100))

Col A Col B Col C Col D Col E Col F
ID1 1 8/1/2009 8/3/2009 8/3/2009 ID1
ID1 2 8/2/2009
ID1 20 8/3/2009
ID2 1 8/4/2009
ID2 2 8/5/2009
ID2 3 8/6/2009


If this post helps click Yes

 

[Jacob Skaria]

Please note that this is an array formula. Within the cell in edit mode (F2)
paste this formula and press Ctrl+Shift+Enter to apply this formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=MAX(IF((A1:A100=F1)*(C1:C100>=D1)*(C1:C100<=E1),B1:B100))


If this post helps click Yes

 

[p45cal]

=SUMPRODUCT(MAX((X2:X5000=A2)*(Y2:Y5000)*(Z2:Z5000>=B2)*(Z2:Z5000<=C2)))

assumes B2 contains start date, C2 contains end date.
You may want to adjust the '>=' and '<=' to just '>' and '<' dependin
on whether you want the dates in B2 and C2 to be included or exclude
from the result

 

[Chris26]

Thanks Jacob and p45cal
Much Appreciated