+ Reply to Thread
Results 1 to 4 of 4

Calculate sums based on date range

  1. #1
    Registered User
    Join Date
    05-09-2017
    Location
    San Diego, CA
    MS-Off Ver
    Standard 2013
    Posts
    3

    Calculate sums based on date range

    Hi. First let me say, I'm a total newbie when it comes to VBA/Macros, I don't even know how to enter them into the worksheet/workbook.
    I am also assuming that this will require a VBA/Macro, if there is an easier way, fantastic!

    I have several hundred entries for one inventory item of when it was received and sold between 2010 and 2017.
    I have 2 versions of the report, the original on Sheet 1, and the "post condense" on Sheet 2, and I need to find the discrepancy, it is off by 1. I can't do a line by line comparison because the condense grouped some items into one inventory adjustment. So I am trying to sum by month.

    This is the formula I came up with:
    For Jan, 2010... SUMIF($A$2:$A$325,">=01/01/2010",$E$2:$E$325)-SUMIF($A$2:$A$325,">=02/01/2010",$E$2:$E$325)
    For Feb, 2010... SUMIF($A$2:$A$325,">=02/01/2010",$E$2:$E$325)-SUMIF($A$2:$A$325,">=03/01/2010",$E$2:$E$325)
    Etc, etc, you get the idea...

    I apply the formula to both versions of the report, then I compare the outcomes to each other IF(G2=Sheet2!G2,"ok","!"). Once it compares and finds the offending month, I can compare the few transactions and find the differences.

    It works quite nicely, but manually changing the column/row info and dates takes forever. I need to use this same formula for over 300 different items that have discrepancies. I can copy and past the 81 versions of this formula (11 per year, 7 years 5 months) that I have already created, but I will inevitably have to change at least the end of range at some point. Find and replace would be usable there, but still, it's time consuming.

    I would like one thing that I can plug in that will look at column A for the Date and Column E for the Quantity and return totals for each month/year in column G, regardless of the date range (whether it starts in 2010 or 2012) and regardless of how many rows there are... if there's a date in column A, it does the math on column E. Then I would like to have it compare the results of the 2 sheets to each other.

    I hope I explained it well enough. I have attached my messy excel sheet in case anyone needs a look.

    Anyway, all help is greatly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: Calculate sums based on date range

    • In any empty cell (K2 for this example) put the start date 1/1/2010, and the next cell below (K3) put the next month 2/1/2010.
    • Select both cells (K2:K3) and drag down to auto-fill to the last month you want summed.
    • In L2 put this formula and drag down to sum the values for each month in column K
      Formula: copy to clipboard
      Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    05-09-2017
    Location
    San Diego, CA
    MS-Off Ver
    Standard 2013
    Posts
    3

    Re: Calculate sums based on date range

    That is very helpful. Thank you. I always forget about the E:E thing to get everything in a column! Duh.

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,495

    Re: Calculate sums based on date range

    Quote Originally Posted by konbanwa View Post
    That is very helpful. Thank you. I always forget about the E:E thing to get everything in a column! Duh.
    You're welcome.

    You could also put this formula in M2 to get the monthly totals from Sheet2. Then you'll have a row-by-row monthly comparison.
    =SUMIFS(Sheet2!E:E,Sheet2!A:A,">="&K2,Sheet2!A:A,"<"&EDATE(K2,1))

+ 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] Calculate # days in week based on date range
    By Peelah in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2016, 07:01 PM
  2. [SOLVED] Calculate based on date and time range
    By local1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 09-25-2015, 08:33 AM
  3. Calculate Effective Date based on Another Date Range
    By Alphabex in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-28-2015, 01:43 PM
  4. [SOLVED] Need formula to calculate monthly costs based on date range
    By NS4Excel in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 07-22-2015, 01:13 PM
  5. [SOLVED] Auto calculate totals and sums based on variable numbers
    By bennadelaidefringe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-03-2015, 12:00 AM
  6. Calculate multiple cells sums based upon another cell
    By swiftworks in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-28-2013, 11:44 AM
  7. Totalfile - calculate sums and place in correct range
    By anvi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-01-2010, 04:57 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