SUMPRODUCT date syntax

[johnturgoose]

I am trying to utilise the sumproduct function and can't seem to get the correct syntax for the date.

I am using:

=SUMPRODUCT((CENTRE=$A4)*(RDATE>C$1)*(RATE<C$2)*75)

I want to add another criteria that looks at the range WITHDRAWN (date). I want it to check that 1 it is null or if not null then it is less than 6 months prior to C2

So the syntax I have used is

=SUMPRODUCT....*(OR(WITHDRAWN="",DATEDIF(WITHDRAWN,C2,"m")<6))

This returns no errors but whatever i enter as a date in the withdrawn range it won't affect the result of the function.

Any advice greatly appreciated. I'm sure its something fairly obvious

Many thanks

John

 

[SkipVought]

Hi,

What is "at the range WITHDRAWN (date). "

Please explain in greater detail what is not working and what your purpose is.

Generally, I you enter a Date in a cell and then reference that cell in your formula, you will get the desired result.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[johnturgoose]

Apologies Skip, It really isn't that clear.

The Formula i am using is

=SUMPRODUCT((CENTRE=$A4)*(RDATE>C$1)*(RDATE<C$2)*75)

Where:

CENTRE is number
RDATE is DATE
WITHDRAWN is DATE (to be added into what I want to do)
$A4 is the centre number
C$1 and C$2 define the date range which I want to include
*75 is just for calculation purposes (each patient gets £75)

This function looks at another sheet and counts all the patients who are at centre $A4 who's randomisation date is between C$1 and C$2. This all works fine. However I have been asked to add the functionality to disclude patients who have withdrawn more than 6 months ago.

So I have added the following

WITHDRAWN - DATE (looks at the same sheet but is a column where the date patient withdrew is entered)
C$3 is the date to be used to exclude the 6 month old withdrawls.

The problem I have is that should the patient have not withdrawn the WITHDRAWN colomn will be empty.

The formula therefore has become

=SUMPRODUCT((CENTRE=$A4)*(RDATE>C$1)*(RDATE<C$2)*(OR(WITHDRAWN="",WITHDRAWN>$C3))*75)

This seems not to exclude anyone regardless of the date I enter in the withdrawn range. Is this just a case of the OR function not working withing the SUMPRODUCT function or me just been stupid.

Hope this is more clear Skip and any help gratefully recieved.

Thanks in advance

John

 

[xlbo]

Without being able to say specifically, ORs are difficult to use in SUMPRODUCT formulae. What I normally end up doing is having 2 formulae - one for each of the OR parts and then adding them together so:

=SUMPRODUCT((CENTRE=$A4)*(RDATE>C$1)*(RDATE<C$2)*(WITHDRAWN>$C3)*75) + SUMPRODUCT((CENTRE=$A4)*(RDATE>C$1)*(RDATE<C$2)*(WITHDRAWN="")*75)


Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[johnturgoose]

Thats the beauty of this site. You can sit in your office pulling out your hair, questionning the marital status of your computer's mother.

Then someone replies with something so simple and obvious that it's almost comical. Thank God for Tek-tips.

Cheers Geoff

John

 

[xlbo]

LOL
Course - I'd prefer a shorter formula with the OR but sometimes it's just easier to use a longer formula that actually works !!

I think the difficulty with ORs is that SUMPRODUCT works on a series of internal AND statements so an OR statement tends to throw it out a bit

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

I'd prefer a shorter formula ...

How about this for shorter:

   =DCOUNT(DATABASE,1,CRITERIA)*75

Test data:
Sheet2: range $A$1:$D$10 named DATABASE
[tt]
CENTRE PATIENT RANDOMISATIONDATE WITHDRAWNDATE
10 A 1/10/05 4/1/04
10 B 1/11/05 4/2/04
10 C 1/12/05 4/3/04
45 D 1/13/05 11/4/04
45 E 1/14/05 11/1/04
45 F 1/15/05 4/6/04
45 G 1/16/05
50 H 1/17/05 4/8/04
50 I 1/18/05 4/9/04
[/tt]

Sheet1: range $E$1:$H$3 named CRITERIA
[tt]
CENTRE RANDOMISATIONDATE RANDOMISATIONDATE WITHDRAWNDATE
45 =">="&$C$1 ="<="&$C$2 =">"&C3
45 =">="&$C$1 ="<="&$C$2 =ISBLANK(Sheet2!D1)
[/tt]

Sheet1: test values:
[tt]
A4: 45
C1: 1/1/05
C2: 1/31/05
C3: =INT(NOW()-183)
[/tt]
The OR relationship is easily handled by having multiple rows in the criteria range.

 

[Zathras]

Sorry, Sheet1 E2 and E3 have this formula:
[tt]
=$A$4
[/tt]
and not hard-coded 45 as it appears in the post.

 

[xlbo]

LOL - always with the "D" formulae !!

Don't know why I don't make use of them more - guess I found something that works and stick with it - SUMPRODUCT is incredibly flexible - and if my dataset gets too big I just put a pivottable over the data instead

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[johnturgoose]

Thankyou for giving this your attention.

The issue with the DCOUNT for me would be that I am copying this formula for approximately 200 centres. I.e $A4 will run down to $A204. I can't see how that would work with the DCOUNT method.

Also I am wondering if people have any tips for improving performance in Excel sheets(barr turning off auto calc). I have dynamic named ranges for all of the ranges. The dataset i am looking at is just over 3000 rows. Would you suggest it would be quicker to name a larger range in them and then adjust rather than have it calculate all the time.

Regards

John

 

[xlbo]

Database functions will calc more quickly than sumproducts - that is at least 1 major advantage that they have...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

There is another little known and underused feature of Excel (originally in Lotus 1-2-3) called "data tables"

Look it up in the help file. It's a bit tricky to set up, so work thru a couple of simple examples first.

Essentially what it allows you to do is specify a range of input values (e.g. centres such as you have) and a formula to be calculated as many times as you have values, displaying the results in a table.

From what you have posted it sounds like a 1-way data table should do the trick for you. (in conjunction with the DCOUNT function).

The alternative is to have 200 criteria ranges (one for each centre) and 200 DCOUNT functions. -- workable but messy. Performance would be about the same because a data table with 200 variables would recalculate about the same as 200 DCOUNT functions. The difference is you can change the calculation method to "Automatic except tables" which may be useful (if you use the data table approach) depending on what else is going on in the spreadsheet.