+ Reply to Thread
Results 1 to 12 of 12

Sum formula for past dates only

  1. #1
    Registered User
    Join Date
    04-18-2021
    Location
    Cambridge, England
    MS-Off Ver
    365
    Posts
    5

    Red face Sum formula for past dates only

    Hi community.

    Hope you're all safe and well. See below please.

    Transaction Date Transaction Type Share Type Ref. No. of Shares Award Share Date Tax Free Date
    16-Nov-16 Award Matching Shares 111/1111 5 16/11/2019 16/11/2021
    16-Nov-16 Purchase Partnership Shares 111/1112 10 16/11/2019 16/11/2021




    This is the setup i have on an excel sheet. What i want to do is calculate the "No. of Shares" dynamically by date. So when they become Tax free i can see the total amount i have available.


    Any help is appreciated. Thank you.

    T

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Sum formula for past dates only

    Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Registered User
    Join Date
    04-18-2021
    Location
    Cambridge, England
    MS-Off Ver
    365
    Posts
    5

    Re: Sum formula for past dates only

    Shares.png

    Have attached an image. Can't submit the file as it has confidential info on it

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Sum formula for past dates only

    We NEVER ask to see sensitive data. Read the instructions again - it talks of a SAMPLE, DESENSITISED workbook with 10 or so rows of representative data.

    Create this and attach it, please.

    Can't read the image, anyway - too small!!!
    Last edited by AliGW; 04-18-2021 at 06:35 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    04-18-2021
    Location
    Cambridge, England
    MS-Off Ver
    365
    Posts
    5

    Re: Sum formula for past dates only

    Is it attached now?
    Attached Files Attached Files

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Sum formula for past dates only

    Yes - thanks.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Sum formula for past dates only

    Your notes seem muddled. There are no matching dates in G, so try this:

    =SUMIF($F$2:$F$9,"<"&$J$1,$E$2:$E$9)

    If that doesn't do what you want, explain again.

  8. #8
    Registered User
    Join Date
    04-18-2021
    Location
    Cambridge, England
    MS-Off Ver
    365
    Posts
    5

    Re: Sum formula for past dates only

    Works fine with that, thanks.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,365

    Re: Sum formula for past dates only

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

  10. #10
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Sum formula for past dates only

    =sumproduct(($e$2:$e$9)*--(today()>$g$2:$g$9))

  11. #11
    Registered User
    Join Date
    04-18-2021
    Location
    Cambridge, England
    MS-Off Ver
    365
    Posts
    5

    Re: Sum formula for past dates only

    Hi I'm back

    What would i need to add to AliGW's formula to return between two dates rather than less than one particular date?

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,001

    Re: Sum formula for past dates only

    Look at SUMIFS ... you should be able to work out.

+ 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. Formula for Past Dates
    By jajejllc in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-10-2019, 11:24 AM
  2. [SOLVED] COUNTIFS formula on past due Dates with conditions
    By rz6657 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-03-2018, 03:56 PM
  3. Replies: 11
    Last Post: 05-04-2014, 08:28 PM
  4. Formula for Evaluations Due and Past Due Dates
    By Mimi8 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2014, 04:52 PM
  5. VBA Formula for Past Due Dates
    By brwnrootsister in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-19-2014, 07:14 AM
  6. Excel Formula to count past due dates
    By zgg500 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-19-2007, 07:13 PM
  7. Formula for finding past dates
    By dmcfarland in forum Excel General
    Replies: 4
    Last Post: 04-11-2007, 12:12 PM

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