+ Reply to Thread
Results 1 to 5 of 5

Add values based on specific dates

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    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?

    Your help is highly appreciated!
    Thank you!
    Attached Files Attached Files
    Last edited by birdmannn; 05-29-2021 at 03:52 PM.

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,310

    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. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,849

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Add values based on specific dates

    Thank you Caracalla for your help! It works!

  5. #5
    Registered User
    Join Date
    10-21-2014
    Location
    London
    MS-Off Ver
    2016
    Posts
    24

    Re: Add values based on specific dates

    Thank you, MarvinP! Appreciate your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. sum based on dates with specific cells
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-19-2019, 11:32 AM
  2. Subtracting values based on dates from specific cases
    By Shellystar1 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-30-2018, 10:09 PM
  3. Replies: 20
    Last Post: 09-13-2018, 12:49 PM
  4. Replies: 13
    Last Post: 05-02-2016, 06:03 AM
  5. Counting specific dates and sub values
    By Leroy221 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-02-2013, 12:23 PM
  6. Replies: 1
    Last Post: 12-14-2011, 11:32 PM
  7. Replies: 3
    Last Post: 03-28-2011, 10:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1