DSUM and DCOUNT over a date range

Discuss the spreadsheet application

DSUM and DCOUNT over a date range

Postby bardolino » Sun Jun 22, 2008 6:22 am

Hi again folks... next problem.

I have a spreadsheet, one of the columns is the date. I need to DCOUNT and DSUM totals for each month. How can I specify a date range in the DCOUNT and DSUM criteria table?

Thanks again for all the help.

Bob
OOo 2.4.X on Ms Windows XP + Mac OS X
bardolino
 
Posts: 4
Joined: Fri Jun 06, 2008 5:17 pm

Re: DSUM and DCOUNT over a date range

Postby squenson » Sun Jun 22, 2008 6:46 am

You could create an additional column called YearMonth, containing the formula =YEAR(date)*100+MONTH(date) -- replace date by the cell containing the date, then drag the formula down the column, so you will get numbers like 200801, 200802, etc. It is then easy to use these numbers for DCOUNT and DSUM.
LibreOffice 4.2.3.3. on Ubuntu 14.04
User avatar
squenson
Volunteer
 
Posts: 1885
Joined: Wed Jan 30, 2008 9:21 pm
Location: Lausanne, Switzerland

Re: DSUM and DCOUNT over a date range

Postby Villeroy » Sun Jun 22, 2008 10:53 am

bardolino wrote:Hi again folks... next problem.

I have a spreadsheet, one of the columns is the date. I need to DCOUNT and DSUM totals for each month. How can I specify a date range in the DCOUNT and DSUM criteria table?

Thanks again for all the help.

Bob

For each month at once? Play with the data pilot on sheet "Pilot" in aggregate.ods
Drag the grey "Month" box onto "Date"

Data pilot with split dates describes an alternative to database reports. Just select your cells and choose "Current Selection" in the first step. The rest is the same.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 30 guests