Return Maximum value for Specific Month(s)

[Sam via OfficeKB.com]

Hi All,

I have a dynamic named range called "Data" that spans 10 columns and many
rows.

I would like to have the maximum value for the month of Jan (January - month
will vary) returned for column number 6. The month is a full date eg:
01/01/2006 using a custom short format "mmm" = Jan.

Column 2 = Full Date formatted as month "mmm"
Column 6 = Numeric Values - max value to be returned
Column 7 = Numeric Values - max value to be returned
Column 8 = Numeric Values - max value to be returned

Cheers,
Sam

 

[Teethless mama]

=MAX(IF(MONTH(A2:A100)=1,B2:B100))

ctrl+shift+enter (not just enter)

 

[T. Valko]

Are there any empty cells in your date column? Empty cells will evaluate as
month 1 (Jan).

Array entered:

=MAX(IF(MONTH(INDEX(Data,,2))=1,INDEX(Data,,COLUMNS($A:F))))

Copy across a total of 3 cells.

For other months replace the 1 with the appropriate month number or use a
cell to hold that number and then refer to that cell.

Biff

 

[Sam via OfficeKB.com]

Hi Teethless mama,

Thank you - very much appreciated.

Cheers,
Sam

 

[Sam via OfficeKB.com]

Hi Biff,

Thank you very much. Works Great!

Cheers,
Sam

Are there any empty cells in your date column? Empty cells will evaluate as
month 1 (Jan).
Array entered:

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Hi Biff,

Thank you very much. Works Great!

Cheers,
Sam