Problems with SUMPRODUCT formula

[TUSSFC]

I have a column of dates in the following UK format:

19/07/2007 15:00:00

I'm trying to calculate, on the fly, the number of cells with a date over 56 days ago. This is the closest I can logically work out myself:

=SUMPRODUCT(--(p_Network!$D$2:$D$50000<DATEVALUE(Today()-56)))

The cell range is on another worksheet within the workbook.

The result it is returning is the TOTAL number of cells, not just the ones over 56 days old.

The forumla looks OK to me ... which is making me think it might be to do with the Date format. I've set the D column to the correct date format as shown in the example above.

Excel version 2003 sp3 for ref.

Any ideas????

 

[qafself]

Hi

try

=SUMPRODUCT(--(p_Network!$D$2:$D$50000<(TODAY()-56))*($D$2:$D$50000>0))

Ed

 

[Kiwiman]

Howzit

How about this. This will count all the cells in your range less than todays date less 56 days. I think anyway...

=COUNTIF(p_Network!$D$2:$D$50000,"<"&TODAY()-56)

 

[TUSSFC]

Thanks for your replies. Both of the above seem to work fine in Excel 07 when I tested it at home last night. But trying it on my master document today in Excel 03 they both return a value of 1.

Any ideas? :-s

 

[Kiwiman]

Mine was tested on Excel 2003, and showed different counts when i changed dates. Is it poss to post a portion of your excel sworkbook?

 

[Brianwarnock]

I think the Countif should be
=COUNTIF(p_Network!$D$2:$D$50000,"<"&TODAY()-"56")

Brian

Seeing Kiwiman's new post tried on Excel 2002 sp3 and my correction works but the original flagged up the 56.
Sumproduct worked.

Countif should be more efficient but probably not noticeable unless massive sheet.

 

[TUSSFC]

See attached - both are actually showing values of zero, not one.

I still think it must be to do with the date values on the data worksheet :-s

 

[Kiwiman]

Howzit

Your dates in the range are date and time stored as text, not actually dates, which is why the formulas are coming up with 0 (comparing a number to a text??). When i highlight the range of dates, the task bar does not show a running sum.

I put the following formula in column e of your range and used that as the reference for the countif \ sumproduct - both came up with 320.

I'm not sure how to do this w/o the column

=DATE(YEAR(D2),MONTH(D2),DAY(D2))

 

[TUSSFC]

I thought it would be something like that. Hmm .. does anyone know how to incorporate the date formatting function into the countif???

I thought about just having a hidden column containing your date formatting function - but then if the corresponding value in column D is null it populates column E with 00/01/1900. This would ruin the countif :-/

 

[Brianwarnock]

I dont think that you can do it with the Countif but you can with the Sumproduct
=SUMPRODUCT((DATE(YEAR(p_Network!$D$2:$D$50000),MONTH(p_Network!$D$2:$D$50000),DAY(p_Network!$D$2:$D$50000))<(TODAY()-56))*(p_Network!$D$2:$D$50000>0))

You do not need to use the unary operator.

Brian

I dont know why there is a space in the MONTH as there isn't in the original or when I attempt to edit!!!!

 

[shades]

Brian, that is a function of the board software. I have run into that several times on many boards.
________
Toyota 7

 

[Brianwarnock]

Hi Rich, nice to see you still pop in.
That space thingy is wierd, why does it do it? I just hope the poster doesn't copy and paste without noticing which is why I commented.

Brian

 

[TUSSFC]

Hi Brian

Thanks for the reply .. it worked perfectly (yes, I noticed the space thing ;-)).

I cannot thank you enough!!!!!