+ Reply to Thread
Results 1 to 16 of 16

Summarise data by date

  1. #1
    Registered User
    Join Date
    03-05-2015
    Location
    Bury St Edunds
    MS-Off Ver
    Office 2013
    Posts
    9

    Summarise data by date

    I have a spreadsheet which contains around 200 entries. In one column for each line is a date. (say column b)
    In another column is a numerical value (column d)

    I want to summarise the data so that at I can easily work out the total of d, but only for those entries where the date falls before April 2016. I can then work out how many fall after April 2016 by subtracting the difference.

    I thought it could be done by pivot tables, but I am struggling. I have Office 2013 and I know the pivot tables are more sophisticated in Office 2016.

    Finally, can I use a value in a pivot table to put into a formula in another cell?

  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,005

    Re: Summarise data by date

    Try

    before 1st April

    =SUMIF(B:B,"<01/04/2016",D:D)

    After (including) 1st april


    =SUMIF(B:B,">=01/04/2016",D:D)
    Last edited by JohnTopley; 01-07-2017 at 08:54 AM.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summarise data by date

    Try something like this...

    Data Range
    B
    D
    E
    F
    G
    H
    1
    Date
    Value
    ------
    Before Date
    After Date
    2
    7/21/2016
    97
    4/1/2016
    113
    282
    3
    1/29/2016
    20
    4
    8/27/2016
    9
    5
    3/8/2016
    57
    6
    5/16/2016
    45
    7
    4/11/2016
    65
    8
    1/12/2016
    36
    9
    12/15/2016
    14
    10
    4/20/2016
    52


    This formula entered in G2:

    =SUMIF(B:B,"<"&F2,D:D)

    This formula entered in H2:

    =SUM(D:D)-G2
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Summarise data by date

    Hi Tony,

    because of the different date formats used in the US, that looks like 4th January in F2.

    Pete

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summarise data by date

    Looking at the dates in column B should give a clue as to the date format!

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summarise data by date

    P.S.

    There should be an international standard!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Summarise data by date

    Like a star date (star trek style)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    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,005

    Re: Summarise data by date

    ISO 8601

    yyyy-mm-dd !!!

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Summarise data by date

    Now, we just need to get that accepted all over

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summarise data by date

    Curious about you Brits...

    When TALKING do you say: my birthdate is the 1st of January 1950.

    Or, do you say: my birthdate is January 1st 1950.

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Summarise data by date

    the 1st 1, Tony
    1st jan 1950

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Summarise data by date

    Well, I would say 6th January 1952, i.e. yesterday I became 65 (wow, seems very old when it is written down).

    Pete

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Summarise data by date

    Happy birthday, Pete, Im 59 in 2 weeks

  14. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summarise data by date

    60 in August!

    And "they" said I wouldn't make it to 20!

  15. #15
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Summarise data by date

    At the risk of hijacking this thread lol, I took you for much younger, Tony

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Summarise data by date

    Quote Originally Posted by FDibbins View Post
    At the risk of hijacking this thread lol, I took you for much younger, Tony
    Well, I don't look my age!

    I have a salt-n-pepper moustache but, if it weren't for the "salt", there's no way I look 60!

+ 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. Replies: 12
    Last Post: 10-09-2016, 11:10 AM
  2. Summarise date of birth
    By mawallace in forum Excel General
    Replies: 1
    Last Post: 03-05-2015, 05:14 PM
  3. Summarise data
    By harignz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2014, 03:35 AM
  4. Summarise data based on date
    By ks04 in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 10-08-2013, 10:54 AM
  5. Summarise data
    By NWSIT in forum Excel General
    Replies: 2
    Last Post: 11-30-2010, 02:42 PM
  6. Pie chart to summarise data
    By t0v3 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-08-2008, 11:05 AM

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