Comparing Dates in SUMPRODUCT function 1

[johnturgoose]

I have a report sheet that looks at data from another sheet.

The function is am using is

=SUMPRODUCT((DATER<$A26)*(DATATRIALS="Leeds"))

Where:
DATER is a date on another sheet
Column A lists months.
DATETRIALS is text

This works fine but isn't that tidy. I want to replace the cell refernce with an actual date eg 01/05/2005. However I am not sure what format I need to use to get this to work.

Any help greatly appreciated.

Many thanks

John

 

[xlbo]

John - just use the DATEVALUE function in conjunction with a text string that excel can interpret as a date - the format is up to you:

=SUMPRODUCT((DATER<DATEVALUE("10/01/2005"))*(DATATRIALS="Leeds"))

Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question

 

[mbarron]

Using the datevalue function is one way.

=SUMPRODUCT((DATER<datevalue("01/05/2004"))*(DATATRIALS="Leeds"))


Mike

 

[johnturgoose]

Thanks guys,

However, using this method means every month when a new row is added I need to manually update the text string to the next month. Is there any way I can have a function that I can copy down and will adjust itself accordingly?

Thanks

John

 

[xlbo]

Surely that's the whole point of using cell references. Text strings in formulae don't increment

However, if it is months you are looking at, maybe this kind of syntax would work for you ??

=SUMPRODUCT((YEAR(DATER)=2004)*(MONTH(DATER)=ROW()-5)*(DATATRIALS="Leeds"))

Amend the row()-5 to suit your needs - just needs a logical comparison comparing the month number to the row number




Rgds, Geoff

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

Please read FAQ222-2244 before you ask a question