Return Maximum value for Specific Month(s)

  • Thread starter Sam via OfficeKB.com
  • Start date
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
 
T

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
 
S

Sam via OfficeKB.com

Hi Biff,

Thank you very much. Works Great!

Cheers,
Sam

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top