+ Reply to Thread
Results 1 to 7 of 7

Need a formula to calculate a total in one column when a date is added in another column.

  1. #1
    Registered User
    Join Date
    11-03-2016
    Location
    Houton, TX
    MS-Off Ver
    Office 360 2016
    Posts
    16

    Need a formula to calculate a total in one column when a date is added in another column.

    Want to total the amount of one column on the condition that a date is entered in another column. See attachment for example. This is to track the total of invoices that have been paid.
    Attached Files Attached Files

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need a formula to calculate a total in one column when a date is added in another colu

    Try this formula in F1.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This only works because dates are numbers. I know of no other test for dates than to test if it is a number. The downside is if you enter any other number that is not a date into column F it will return incorrect results in F1.

    You also have column F (including F1) formatted as Text. This can cause problems for calculations later. Try changing the formatting for F1 to General. You may need to recommit that formula. Then apply Text to columns to the rest of the data in column F (it's in the Data ribbon) > choose Delimited > Next > then clear all the delimiter boxes > Next > then choose the date format > Finish.
    Dave

  3. #3
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need a formula to calculate a total in one column when a date is added in another colu

    This is a duplicate thread.

  4. #4
    Registered User
    Join Date
    11-03-2016
    Location
    Houton, TX
    MS-Off Ver
    Office 360 2016
    Posts
    16

    Re: Need a formula to calculate a total in one column when a date is added in another colu

    That does not work.
    Better explanation of what I need. In the attached file it should be E not F.
    When a date is added in column E, it will add total amount from column C to running total in F1. If the cell is blank nothing is done but once the date is added it will add invoice amount to total.

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Need a formula to calculate a total in one column when a date is added in another colu

    Thank you. Your in-sheet instructions said column "F". I wondered about that.

    When a date is added in column E, it will add total amount from column C to running total in F1.
    Column C are dates. Do you mean column B? Try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-03-2016
    Location
    Houton, TX
    MS-Off Ver
    Office 360 2016
    Posts
    16

    Re: Need a formula to calculate a total in one column when a date is added in another colu

    Thank you FlameRetired that worked perfectly.

  7. #7
    Registered User
    Join Date
    12-06-2016
    Location
    India
    MS-Off Ver
    2007
    Posts
    72

    Re: Need a formula to calculate a total in one column when a date is added in another colu

    You can also use sumif formula here

    =SUMIF(E4:E18,"="&"",B4:B18)


    Need more help? Visit livespreadsheethelp.com for free, instant match, personalized help from paid certified experts in Excel.
    Last edited by guru008; 12-07-2016 at 12:56 AM.

+ 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. [SOLVED] Need vba or excel formula to calculate total sales with percentage in L:L column
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-01-2016, 03:36 AM
  2. Formula to calculate % of total sum in column
    By mxlew in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-01-2015, 11:38 AM
  3. Formula to calculate total from column with text
    By Sadalif in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-26-2013, 01:52 PM
  4. Replies: 5
    Last Post: 07-06-2013, 08:21 PM
  5. Replies: 3
    Last Post: 01-25-2009, 12:45 PM
  6. Replies: 0
    Last Post: 02-04-2005, 03:58 PM
  7. Formula to calculate a total in one range based on dates from another column
    By CarolineD in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-01-2005, 04:56 PM

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