Formula for Latest Entry For Each Month

[foofoo]

Hello -

My spreadsheet has a list of dates and data in rows 9 through 500.
Dates are shown in Column A of these rows and data is in Column Q of
these rows. There is also a summary section in row 6, and I would
like to enter a formula in row 6 that will show the latest entry in
Column Q by month.
An example of the spreadsheet layout as well as the results to
populate in row 6 are shown below.

Nov 2007 (Col C) Dec 2007 (Col D) Jan 2007
(Col E)
Row 6 10.45 12.25
3.20

Date (Col A) Data (Col Q)
Row 9 11/27/07 10.45
Row 10 11/28/07
Row 11 11/29/07
Row 12 11/30/07
Row 13 12/01/07 12.25
Row 14 12/05/07
Row 15 01/02/08
Row 16 01/30/08 3.20

Any suggestions?

 

[T. Valko]

Need some clarification.

Your subject line says: Formula for Latest Entry For Each Month

So, I assume by that, that there may be more than 1 entry per month.

Are these column headers TEXT entries or are they formatted DATE entries:

Nov 2007...Dec 2007...Jan 2007

Where are these headers, row 5?

 

[foofoo]

Need some clarification.

Your subject line says: Formula for Latest Entry For Each Month

So, I assume by that, that there may be more than 1 entry per month.

Are these column headers TEXT entries or are they formatted DATE entries:

Nov 2007...Dec 2007...Jan 2007

Where are these headers, row 5?

--
Biff
Microsoft Excel MVP










- Show quoted text -

Hi - You are right on all counts. There can be more than 1 entery for
each month. For example, there could be an entry for 11/28/07 and
11/29/07, but only the entry for 11/29/07 should populate in the
summary section. Nov 2007; Dec 2007; and Jan 2008 are column titles
and they are in row 5; they are in text format. Thanks!

 

[T. Valko]

Try one of these array formulas** :

This first formula will return an error if there are DATES but no values in
column Q for that particular month, or, if there are no DATES that match the
column header:

=INDEX($Q9:$Q16,MATCH(MAX((TEXT($A9:$A16,"mmm
yyyy")=C5)*($Q9:$Q16<>"")*$A9:$A16),$A9:$A16,0))

This longer formula traps those errors and will return a blank cell:

=IF(SUM((TEXT($A9:$A16,"mmm
yyyy")=C5)*$Q9:$Q16),INDEX($Q9:$Q16,MATCH(MAX((TEXT($A9:$A16,"mmm
yyyy")=C5)*($Q9:$Q16<>"")*$A9:$A16),$A9:$A16,0)),"")

Enter either formula in C6 and copy across as needed.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

Note that in both formulas there is a space in this string:

"mmm yyyy"

Line wrap might make them appear as:

mmm
yyyy