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

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