DSUM: Office/Date Table Not Summing Properly

isadoko

Active Member
Joined
Jan 10, 2005
Messages
318
OS: XP
XL: 2003

This is my first time using DSUM. I have crafted this formula:

=DSUM('B385 Ledger'!A:C,"Amount",'B385 Open Balance'!X1:Y171)

My data table is set up thus (dates range 12/30/04 to 1/20/05):

Office No Date Amount
2 12/30/04 (1,088.00)
3 12/30/04 (169.00)
6 12/30/04 703.00
8 12/30/04 (66.00)
9 12/30/04 (324.29)

My "results" table looks thus (dates range 12/30/04 to 1/20/05):
Office No Date
1 >=12/1/04
1 <=12/31/04
2 >=12/1/04
2 <=12/31/04
3 >=12/1/04
3 <=12/31/04

Naturally I have a cell containing the DSUM formula (result).

I understand DSUM to read the headings ("office No" and "Date") and the data below to calculate: not so...thus far. It seems unable to interpret the date bands (>=12/1/04 to <=12/31/04)assigned to each office number. I was told I needed "start" and "end" dates to limit my search with corresponding office numbers.

My sample holds in part 22 offices, all dated 12/30/04. The balance of 60 offices all show January 2005 dates. The rub is my formula is summing the December entries though my date band is >=1/1/2005 to <=1/31/2005.

As this table will grow throughout 2005 I want to sum up each office's monthly total. I had in mind a table with column of office numbers and rows of months.

What am I doing wrong? I look forward to your response and many thanks.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Scott R

Active Member
Joined
Feb 20, 2002
Messages
443
Office Version
  1. 365
Platform
  1. Windows
If you want dates between 12/1 and 12/31, you can't stack the criteria because that would mean 'or'; it has to be side-by-side for 'and'.

Office Date Date
1 >=12/1/04 <=12/31/04
 

Forum statistics

Threads
1,148,390
Messages
5,746,432
Members
424,016
Latest member
Shizzl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top