Calculating sequences of criteria for the Excel DSUM function

Criteria for Microsoft Excel DSUM Formulae

Question

We are running a database in Excel and want to use the DSUM function to calculate totals over ranges of dates on a month-by-month basis. This means we have to set up a DSUM formula for each month and this we are doing longhand at the moment. Is there a way we can set up a formula that we can copy to save us all the longhand work?


Answer

This screen dump illustrates the data in the table (much simplified!) upon which you want your DSUM formula to work:

Excel Spreadsheet

And this one shows how you have set up the criteria in columns B and C, used by the DSUM formulae in column D:

Criteria Spreadsheet

The formula in cell D2 is this (we note that you use the range name 'Datatable' applied to all the data in your main table, including the 'Date' and 'Value' column headings in row 1):

=DSUM(Datatable,2,B1:C2)

What you want to be able to do is copy the last month's block of cells to create a new month (ie, copy the A5:D6 block to cell A7 to create a block for April).

This is the setting up you need to complete:

  • In cell A2, input 1/1/2001
  • Format cell A2 with a custom date format of mmm yyyy
  • In cell A4, input the formula =DATE(YEAR(A2),MONTH(A2)+1,1)
  • In cell B4, input the formula =">="&TEXT(A4,"dd/mm/yyyy")
  • In cell C4, input the formula ="<"&TEXT(DATE(YEAR(A4),MONTH(A4)+1,1),"dd/mm/yyyy")
  • In cell D4 is your formula copied from cell D2 =DSUM(Datatable,2,B3:C4)

Now, copying and pasting the range of cells A3:D4 to A5, A7, A9 and so on, will produce the results you require. No additional inputting is necessary.

Hiding columns B and C, and all the odd-numbered rows, results in a neat summary:

DMW Spreadsheet


Disclaimer

DMW Consultancy Ltd does not accept any liability for loss or damage to data to which any techniques, methods or code included in this website are applied. Back up your data; test thoroughly before using on live data.