OK so I have been racking my brain for weeks trying to figure out how to extract totals from a daily report. Basically I have a sheet that I refresh daily to bring in the data from a text file. In that data is a Source Name field, a Exit Date field, and a Quantity field. Without messing up the data sheet (because it changes daily and I have to keep refreshing it) I need to have the monthly total calculated. I want to be able to have someone else that knows nothing about excel to be able to just open, refresh, and send. I have been messing around with DSUM() and Pivot Table then using GETPIVOTDATA() functions with little success. I put something together using DSUM() but it is clunky and isn't very dynamic when it comes to adding sources. If I could some how put an variable inside my GETPIVOTDATA() function that would only total quantities under a particular source name where exit date is between 02/01/11 and 03/01/11. I am using Excel 2003 and here is what the data looks like. Thanks for your help.


Data (Refreshed Daily)
SOURCE NAME Exit Date QUANTITY
Source 1 1/6/2011 75
Source 1 1/21/2011 190
Source 1 1/21/2011 1480
Source 1 1/21/2011 2750
Source 1 2/19/2011 2000
Source 1 2/19/2011 2500
Source 1 2/19/2011 1000
Source 1 3/3/2011 3000
Source 1 3/3/2011 1500
Source 1 3/17/2011 2500
Source 1 3/17/2011 500
Source 1 3/17/2011 3000
Source 1 3/24/2011 4100
Source 1 3/24/2011 1200
Source 1 3/24/2011 2000
Source 1 2/17/2011 1000
Source 1 2/24/2011 1000
Source 1 4/7/2011 1000
Source 1 4/7/2011 2800
Source 1 4/7/2011 1400
Source 1 4/7/2011 1800
Source 1 4/7/2011 3000
Source 1 4/7/2011 500
Source 2 3/3/2011 401
Source 2 3/3/2011 584
Source 3 3/10/2011 20
Source 3 3/10/2011 50
Source 3 3/10/2011 10
Source 3 3/10/2011 10
Source 3 3/31/2011 50
Source 3 3/31/2011 50
Source 3 3/31/2011 50
Source 3 3/31/2011 60
Source 3 3/31/2011 60
Source 3 3/31/2011 30
Source 3 3/31/2011 60
Source 3 3/31/2011 35
Source 4 2/12/2011 1175
Source 4 2/17/2011 30
Source 4 2/17/2011 20
Source 4 2/17/2011 25
Source 4 2/17/2011 25
Source 4 2/17/2011 20
Source 4 2/17/2011 30
Source 4 2/17/2011 20
Source 4 2/17/2011 25
Source 4 2/17/2011 30
Source 4 2/17/2011 10
Source 4 2/17/2011 5
Source 4 2/17/2011 5
Source 4 2/17/2011 10
Source 4 2/17/2011 10
Source 4 2/17/2011 5
Source 4 2/24/2011 1000
Source 4 2/24/2011 75
Source 4 2/24/2011 100
Source 4 2/24/2011 75
Source 4 2/24/2011 25
Source 4 3/10/2011 950
Source 4 3/10/2011 250
Source 4 3/10/2011 150
Source 4 3/10/2011 50
Source 4 3/10/2011 175
Source 4 3/10/2011 40
Source 4 4/7/2011 75
Source 4 4/7/2011 400
Source 4 4/7/2011 600
Source 4 4/7/2011 100
Source 4 4/14/2011 1000
Source 4 4/14/2011 400
Source 4 4/28/2011 200
Source 4 2/15/2011 200
Source 4 2/15/2011 100
Source 4 2/15/2011 250
Source 4 2/15/2011 50
Source 4 2/15/2011 50
Source 4 2/15/2011 50
Source 4 2/15/2011 100
Source 4 2/15/2011 50
Source 4 4/17/2011 50
Source 4 4/17/2011 50
Source 4 4/17/2011 100
Source 4 4/17/2011 50
Source 4 4/17/2011 50
Source 4 4/17/2011 50
Source 4 4/17/2011 50
Source 4 4/17/2011 30
Source 4 4/17/2011 200
Source 4 6/24/2011 1225
Source 4 5/20/2011 762
Source 4 3/17/2011 50
Source 4 3/17/2011 100
Source 4 3/17/2011 50
Source 4 3/17/2011 50
Source 4 3/17/2011 50
Source 4 3/24/2011 249
Source 4 3/24/2011 167
Source 4 3/24/2011 167
Source 4 3/24/2011 167
Source 4 3/7/2011 245
Source 5 1/31/2011 97
Source 5 1/31/2011 95
Source 5 2/17/2011 30
Source 5 2/17/2011 30
Source 5 2/17/2011 290
Source 5 2/17/2011 128
Source 5 2/17/2011 217
Source 5 2/17/2011 109
Source 5 2/17/2011 192
Source 5 2/17/2011 3
Source 5 2/17/2011 169
Source 5 2/17/2011 237
Source 5 3/10/2011 100
Source 5 3/10/2011 213
Source 5 3/10/2011 100
Source 5 3/10/2011 30
Source 5 3/10/2011 30
Source 5 3/10/2011 20
Source 5 3/10/2011 30
Source 5 3/10/2011 100
Source 5 3/10/2011 100
Source 5 3/10/2011 100
Source 5 3/10/2011 550
Source 5 3/10/2011 100
Source 5 3/10/2011 400
Source 5 3/10/2011 100
Source 5 3/10/2011 100
Source 5 3/10/2011 150
Source 5 3/10/2011 100
Source 5 3/10/2011 75
Source 5 3/31/2011 225
Source 5 3/31/2011 150
Source 5 3/31/2011 500
Source 5 3/31/2011 400
Source 5 3/31/2011 75
Source 5 3/31/2011 125
Source 5 3/31/2011 60
Source 5 3/31/2011 60
Source 5 3/31/2011 150
Source 5 4/10/2011 175
Source 5 4/10/2011 250
Source 5 4/8/2011 200
Source 5 4/8/2011 175
Source 5 4/8/2011 225
Source 5 4/8/2011 251
Source 5 3/15/2011 300
Source 5 3/15/2011 400
Source 5 4/15/2011 162
Source 5 4/15/2011 300
Source 5 4/15/2011 130
Source 5 4/15/2011 188
Source 5 4/15/2011 224
Source 5 4/15/2011 225
Source 5 4/15/2011 375
Source 5 4/15/2011 75
Source 5 4/15/2011 75
Source 5 4/14/2011 200
Source 5 4/14/2011 200
Source 5 4/30/2011 800
Source 5 4/30/2011 700
Source 5 4/30/2011 100
Source 5 4/30/2011 200
Source 5 4/30/2011 100
Source 5 4/30/2011 300
Source 5 4/30/2011 75
Source 5 4/30/2011 100
Source 5 4/30/2011 225
Source 5 4/28/2011 210

I want it to populate a grid that looks like this:


Analysis
Total Earlier Feb-11 Mar-11 Apr-11 May-11 Jun-11
Source 1
Source 2
Source 3
Source 4
Source 5

Dear Frost240z, Good Afternoon.

The SUMPRODUCT FUNCTION is well designed to solve this kind of question.
This function is easy to use.

I did an example for you using Excel 2003.
It´s he 08-02-2011_ExcelBanter_SUMPRODUCT_FROST240z_v1-0.xls

Take a look at it and tell me if it worked for you.
Fell free to ask anything about it.