This is the first time I've tried to use DSUM (in a real scenario) and I am having trouble getting the criteria correct. The attached spreadsheet shows what I've got at the moment DSUM Example.xlsx
The source data is on the Data tab, which also contains my practice DSUM in I2.
The DSUM Criteria sheet contains, you guessed it, the DSUM criteria. I am trying to find all items where 'Termination Code' and 'Termination Date' are blank, or where the 'Termination Date' is greater than a particular date. The way I understood it, I need to list the combinations of data that I want to be included in the sum, so you will see I have repeated =New Business, =Renewal and =Transfrd NB for each of the 'Termination Code' values that I want to include.
The 'Customer Class', 'Transaction Type', 'Effective year' and 'Effective Month' criteria seem to be working. I think the problem lies with 'Termination Code' and 'Termination Date'. I've left the top 3 'Termination Code'/'Termination Date' values ('DSUM Criteria'!B2:C4) blank to try to facilitate this, however, I think that is nullifying the criteria in rows 5:25.
Question 1: is that correct, and how should I specify I want blanks as well as specific combinations of 'Termination Code' and 'Termination Date'?
The Data has been filtered by Transaction Type, Effective Year and Effective Month to try to identify why it isn't working. Rows 221, 250, 309 and 576 are examples that should be excluded from the DSUM result.
Question 2: ideally, I want the 'Termination Date' criteria to compare to another value on the same data row, instead of the hardcoded '31/07/2011' that I currently have in there (i.e. 'Termination Date' criterion should be 'btxdtraised' + 1 year); is this possible?
Question 3: is there a better method to use instead of DSUM?
Bookmarks