How To Match Dates

[Robert Gillard]

On worksheet1, I have a several columns (A-M) one of which (colA) is the
daily date in the format of 21-Nov-2004 so by the end of a year there will
be approx 365 rows.

On worksheet2, there is a single row monthly summary of the above data (so
sheet1 has approx 30 rows per month while worksheet2 has a one row summary)
where the date on this second worksheet is headed as
Month Begining
(A1) 01-Nov-2004,
(A2) 01-Dec-2004,
(A3) 01-Jan-2005

I now need reference a date from sheet1 to the summary on sheet2, so if I
input 12-Jan-2005 on sheet1, I need to have the monthly summary row from
sheet2 to infill.

So if I input any date from 1Jan to 31Jan then it would return the same row
from sheet2 in this example Month Begining 01-Jan-2005.

Could anybody help in this regard,

With Thanks

Bob

 

[Guest]

Hi Robert,

I think I have a solution for you..
I assumed the values you were lookign to sum are in Column B of Sheet 1
where the dates are stored.

Try the following formula in Sheet 2 col B and replicate down to all the
associated rows next to the month Summaries:
{=SUM((MONTH(Sheet1!$A$2:$A$366) = MONTH(Sheet2!A2))*Sheet1!$B$2:$B$366)}


Note: the {} brackets indicate that it is an array formula. You do not
include them in the cell but are automatically included when you use the Ctr
+ Shift + Enter Key.
Sorry if you already know this..

Also, this does not distinguish between different years. You could add the
same logic to do that.

Hope this helps..

GG