Max Value, Sum Product, Between Date Range

  • Thread starter Thread starter Chris26
  • Start date Start date
C

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
 
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
 
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
 
Back
Top