DSUM: Office/Date Table Not Summing Properly

isadoko

Active Member
Joined
Jan 10, 2005
Messages
322
Office Version
  1. 365
Platform
  1. Windows
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Scott R

Active Member
Joined
Feb 20, 2002
Messages
449
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,181,439
Messages
5,929,916
Members
436,705
Latest member
Priti19

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