# Add values based on specific dates

1. ## Add values based on specific dates

Hi everyone,

Hope there is someone who can help me with the following problem. Please find my file attached.

I have a table that contains dates in column A and some values in column B. I am trying to add values that belong to the earliest and the latest date in a specific month and year.

Just to give you an example, the earliest day in February 2015 that I have in my table is 02.02.2015 with the corresponding value of 57. The latest day in February 2015 that I have in my table is 27.02.2015 and the corresponding value is 66. Now I need to add 57 and 66 but to do that I need to find the earliest and the latest dates, all based on the specific month and year. The first day in the month is not necessarily the 1st as much as the last day in the month is not necessarily the 28th, 30th or 31st. It all depends on available dates in column A.

Is there a way to do this dynamically, i.e. by referencing cells that contain different months and years?

Thank you!

2. ## Re: Add values based on specific dates

F2=IFERROR(SUM(INDEX(\$B\$1:\$B\$25,MATCH(AGGREGATE(14,6,\$A\$1:\$A\$25/(MONTH(\$A\$1:\$A\$25)=MONTH(F\$1&0))/(YEAR(\$A\$1:\$A\$25)=\$E2),1),\$A\$1:\$A\$25,0)),INDEX(\$B\$1:\$B\$25,MATCH(AGGREGATE(15,6,\$A\$1:\$A\$25/(MONTH(\$A\$1:\$A\$25)=MONTH(F\$1&0))/(YEAR(\$A\$1:\$A\$25)=\$E2),1),\$A\$1:\$A\$25,0))),"")

Copy across and down

3. ## Re: Add values based on specific dates

Hii birdmannn,

See the attached where I needed two helper columns to do your problem You must first sort column A from small to large then in column C is a countif() the date is the first one of that month. Column D uses the Date() function to remove non first days and roll them back to the first of that month. Then the Early date is calculated using an Index(Match()) combination. The Late date is much easier using a VLookup with a True (non exact) match using the last day of the month. See the attached.
File dates for Early and Late per month.xlsx

I was wondering if an Aggregate function might do what my procedure does and I see Cara figured it out above. Take your pick on possible solutions.

4. ## Re: Add values based on specific dates

Thank you Caracalla for your help! It works!

5. ## Re: Add values based on specific dates

Thank you, MarvinP! Appreciate your help!

There are currently 1 users browsing this thread. (0 members and 1 guests)