Hello,
I’m learning about Database functions – specifically DSUM - and I’m stuck at how to enter in the ‘criteria section’ dates between e.g. Sum of sales from 18th October 2014 to 17th October 2015.



I’ve set up the criteria section at the top of the worksheet, above the data table, with several blank lines to enter the criteria into.

One of the fields in the data table is ‘Order Date’ with a list of dates.

I’m not sure how to enter the criteria I’m looking for, as its between two dates, and I’m not sure how to format this correctly.

Any help would be greatly appreciated. Thanks in advance.
Cheers

NB! SumRange and OrderDateRange must be of same dimension, i.e. must have same number of rows!

Hi there,

Thanks for replying to my post – its much appreciated.

I’m sorry if I was unclear in my description of the problem I’m having. It’s a DSUM function I’m learning at the moment. It has 3 arguments: database,field,criteria.

I’ve got a table of data with headers. I’ve copied and pasted the headers into another section of the worksheet with blank rows below to enter my criteria.

There are 7 fields in my table i.e. Order Date; Region; Rep; Item; Units; Unit Cost; Revenue.

I’ve created a text box labelled DSUM and the cell next to it is where I have my DSUM formula entered. I’ve managed to work out how to calculate other questions e.g. calculate total revenue for the region ‘Central’ but only for the item ‘Pen’.
I enter ‘Central’ below the Region heading in the Criteria Section and ‘Pen’ below the Item heading and arrive at the correct result.

What I’m having difficulty with is how and where to enter from 18th October 2014 to 17th October 2015. I presume it needs to read something along the lines of: >=18/10/2014 and <=17/10/2015? As I’m entering two dates in one box under Order Date in the Criteria Section, I’m not sure how I should type this?

I hope I’ve explained this a little better? Thanks

Have never used DSUM(), and after I had a look at it, I'll never use it. It does nothing that SUMIFS() doesn't do, and you are less free at design with it.

And before SUMIFS() was introduced, I used SUMPRODUCT() instead.

Thanks anyway...Cheers

You need to duplicate the Order Date header in another column and put the >= criterion in one column and the <=criterion in the other.

Alternatively, you can use a formula criterion instead.

Hello,
Thanks so much for replying to my post. It’s been causing me no end of a headache!

To explain, I’m doing a free online Excel course. The module I’ve been working on is how to use database functions. It doesn’t cover everything and just skims through certain topics. It only showed how to use the DCOUNT function, and while trying out different database functions e.g. the DSUM for example, which I found more interesting and useful, I came across a website www. wallstreetmojo.com and downloaded a DSUM examples template. It had a number of questions with the results shown in Revenue boxes on the right hand side of the worksheet. The formulas were the same with database, field, criteria arguments entered.

Question 5 – “Sum of sales from 18th October 2014 to 17th October 2015” required two separate Order Date boxes to enter >=18/10/2014 and <=17/10/2015 as you’ve explained here. The problem was that in the Revenue box with the formula showing as correct, in my downloaded template worksheet the result was displaying as 0. I checked online to the original website and it had a result of 8646.

I tried various ways of creating the Criteria Section e.g. above the table; to the right as shown in the website; but nothing produced the correct result. When I click in the Revenue cell in the downloaded template worksheet, the correct formula is showing. I’ve checked and double checked this!

In desperation, I tried another way to see if I could get the correct result matching the website. I filtered the dates and then added a total row to get the sum, and in the Revenue column – voilà!

I’ve tried what you’ve described here and duplicated the OrderDate header to another column and entered the dates i.e. >=18/10/2014 and <=17/10/2015. The duplicated column is named OrderDate 2 by default. I tried it duplicating just the column header in the criteria section, and not adjusting the table headers. I also tried it by duplicating both, to keep the headers identical in both table and criteria section. Still not getting the 8646 as the result! Could this be anything to do with me having Home & Student 2013 and not the full Office 365 version when it comes to entering two criteria in the same column?

Anyway, when it comes to entering the arguments in the Function Arguments dialogue box, how would I enter in the field box, the two OrderDate column numbers or headings?

I’m also not sure what you mean by using a formula criterion alternatively? Instead of using the Function Arguments box, I tried to enter the formula in the formula bar, but not getting this right…too many arguments!

Sorry this is so long, but I really want to be able to use this database function as it’s supposed to be used. I’m stubborn that way!! Many thanks.
Cheers

It sounds like you have your criteria range set up as a table. You don't want that because it will stop you from duplicating column headers.

It would be simplest if you post your workbook so that we can see where you are going wrong currently. I will then also show the formula criterion version. It's a little odd, but can make complex criteria easier to set up.

Success! I converted the table back to a normal range and then duplicated the Order Date column as you suggested, and then copied and pasted the criteria headers elsewhere on the worksheet. While doing this and looking at the example template I’d downloaded, I noticed the table dates were in the UK date format but the criteria dates entered were in US date format. When I changed them to UK format the result immediately changed from 0 to 8646.

For some reason when clicking on ‘enable editing’ after scanning the downloaded template, this is when the value changed. Before that it shows as 8646! So, that’s good to know for the future to double check everything as I’m not 100% observant when it comes to that sort of thing.

You’ve been a great help. I wouldn’t have worked this out myself or discovered the date format discrepancy if I hadn’t gone back to look at this again and duplicate the column etc. Thanks a million.
Cheers