# Calculate sums based on date range

1. ## 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!

2. ## 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:
`Please Login or Register  to view this content.`

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. ## Re: Calculate sums based on date range

Originally Posted by konbanwa
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))

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

#### 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