+ Reply to Thread
Results 1 to 14 of 14

2003 Excel - How to add one column based on a date range in another column

  1. 10-21-2014, 01:43 AM #1
    Skumby
    Skumby is offline
    Registered User
    Join Date
    04-30-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    2003 Excel - How to add one column based on a date range in another column

    Hiya,

    I have a spreadsheetI use for gas bottle deliveries. I need to be able to calculate the total number of picked up bottles that occuered between two dates.

    For example...
    I have a sheet called "Pick Ups".
    I have a sheet for 21/10/14 deliveries called "211014".
    I have a section on "211014" to add the amount of picked up bottles we did for that week period.
    So I need to add all the numbers in column G5:G21 in "Pick Ups" but only the ones that occur between the dates in cell A1 and A3 on "211014".
    Reply With QuoteRegister To Reply
  2. 10-21-2014, 01:46 AM #2
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: 2003 Excel - How to add one column based on a date range in another column

    Could you please provide sample workbook with output.
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
    Reply With QuoteRegister To Reply
  3. 10-21-2014, 01:50 AM #3
    Skumby
    Skumby is offline
    Registered User
    Join Date
    04-30-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: 2003 Excel - How to add one column based on a date range in another column

    Gas Pickup and Deliveries Log DUMMY.xls

    Hopefully the attachment worked.
    Reply With QuoteRegister To Reply
  4. 10-21-2014, 01:59 AM #4
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: 2003 Excel - How to add one column based on a date range in another column

    what is your calculation & where u want the output from whcih sheet to which sheet
    Reply With QuoteRegister To Reply
  5. 10-21-2014, 02:02 AM #5
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: 2003 Excel - How to add one column based on a date range in another column

    something like this
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 10-21-2014, 02:17 AM #6
    Skumby
    Skumby is offline
    Registered User
    Join Date
    04-30-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: 2003 Excel - How to add one column based on a date range in another column

    Gas Pickup and Deliveries Log DUMMY.xlsNot quite.

    I need the formula in 211014/F4
    Based on the numbers that I have entered in Pick ups/I5:I21, but only the ones that the date occurs between cells 211014/A1 and I1.
    I enter the numbers in the Pick Ups sheet under the 13.5kg, 15kg and 45kg headings based on how many bottles were purchased.
    Reply With QuoteRegister To Reply
  7. 10-21-2014, 02:31 AM #7
    boopathiraja
    boopathiraja is offline
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: 2003 Excel - How to add one column based on a date range in another column

    Hi, You mean this in F4 ?

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    ☚ Click ★ just below left if it helps, Boo?ath?
    Reply With QuoteRegister To Reply
  8. 10-21-2014, 02:44 AM #8
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: 2003 Excel - How to add one column based on a date range in another column

    Try this
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  9. 10-21-2014, 02:45 AM #9
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: 2003 Excel - How to add one column based on a date range in another column

    Hi

    Try this
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 10-21-2014, 03:06 AM #10
    Skumby
    Skumby is offline
    Registered User
    Join Date
    04-30-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: 2003 Excel - How to add one column based on a date range in another column

    Thank you so much Boopathiraja!! That worked a treat!
    Reply With QuoteRegister To Reply
  11. 10-21-2014, 03:07 AM #11
    Skumby
    Skumby is offline
    Registered User
    Join Date
    04-30-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: 2003 Excel - How to add one column based on a date range in another column

    I am unfortunatly working with 2003 Exel Naveed, so SumIfs doesn't work. Hopefully the company I work for step forward into the 21st century and update their Microsoft!
    Reply With QuoteRegister To Reply
  12. 10-21-2014, 03:08 AM #12
    boopathiraja
    boopathiraja is offline
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: 2003 Excel - How to add one column based on a date range in another column

    Hi, Naveed Raza the op's wants the formula for 2003 version, so sumifs can't be used, may be sumproduct or sum(if( array version
    Reply With QuoteRegister To Reply
  13. 10-21-2014, 03:08 AM #13
    boopathiraja
    boopathiraja is offline
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: 2003 Excel - How to add one column based on a date range in another column

    Hi thanks for the feedback, If your Query Solved, You can mark the thread as solved and (preferably) say thanks to those who helped by clicking the Add Reputation button.
    Reply With QuoteRegister To Reply
  14. 10-21-2014, 03:29 AM #14
    Naveed Raza
    Naveed Raza is offline
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: 2003 Excel - How to add one column based on a date range in another column

    Thanks for the information, however skumby mention MS-Office version as 2010 so i though to give SUMIF its very easy to understand.

    anyways Sumproduct is statnd function its work in all versions.

    @Skumby
    Please Mark the thread as SOlved and say thanks to those who helped by clicking the Add Reputation button
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel 2003 - Returning the date in column for matching values
    By SunRay in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-07-2014, 02:44 PM
  2. Calculate a range of $ based on cumulative reference # from another column - Excel 2003
    By rajncajn in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-07-2013, 06:05 PM
  3. [SOLVED] SUM data in column based on Date/Time range
    By t.jones in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-11-2013, 06:43 PM
  4. filtering date column based on the range
    By kmksprasad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-26-2012, 05:04 PM
  5. EXCEL Sum column based on time range in different column?
    By RD975 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-13-2005, 01:05 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1