Using DSUM with date range

**Altin** · 09-22-2010, 11:22 AM

I am using a spreadsheet to track & monitor our household expenses. On one sheet have columns for date (as 22/09/2010), category, income & expense. On the second sheet, I have a column for each category of income/expense, & rows for Jan, Feb, Mar, etc.

In the second sheet I have been using SUM & IF statements to calculate where the category in the first sheet is ‘Phone’, if it is greater than 01 Mar & if it is less than 31 Mar, then add together all corresponding values in the expense column. All these calculations make the spreadsheet wrok very slowly when I change something, & I was wondering if the DSUM formula would work for this? How do I get DSUM to use a range of dates (1 – 31 Mar) as a criteria?

An example of the formula I am currently using is: {=SUM(IF(Sheet1!B1:Sheet1!B5000=A14,IF(Sheet1!A1:Sheet1!A5000>=DATE(2010,3,1),IF(Sheet1!A1:Sheet1!A5 000<=DATE(2010,3,31),Sheet1!E1:Sheet1!E5000,""))))}

**Cheeky Charlie** · 09-22-2010, 11:35 AM

Use a pivot table on your source data and group results by month - if you upload an example I can show you how. (Don't use DSUM).

**Altin** · 09-23-2010, 06:57 AM

Hi

Have attached example of spreadsheet. Can you explain also what you are doing please, as I am very unfamiliar with pivot tables.

Thanks

**Cheeky Charlie** · 09-25-2010, 01:18 PM

Thanks for adding examples I downloaded your first spreadsheet and decided to come back to it when I had time to add content to demonstrate with - could have been never.

Step-by-step (as requested):
I insert a dynamic named range for the source data - an explanation is here. The formula I used: =offset($A$1,0,0,counta($A:$A),5)
Then I created a pivot table (alt, d, p)
Next
The source is the name I just created (I called it Data)
New worksheet
Finish

Drag Date to the column fields area
Right click a date, Group and show detail, group
Group by months
OK

Drag category to the row fields

Now, you could make two pivot tables, looking like the example you uploaded, but what I think might work better is creating a new calculated field incorporating both negative and positive cashflow - so on the pivot table menu bar:
Pivot table
Formulas
Calculated Field
I called it Cashflow
Formula='Amount In'-'Amount Out'
it should appear magically in the 'data' area of the graph - if not drag it there

Right-click the sum of cashflow button
Field settings
Number
Currency

OK

I think that's all you need, you can autoformat the table with Format ->Autoformat

hth