SUMPRODUCT problem

[×לי]

Hi!

I am using the SUMPRODUCT fuction with the following table:
=SUMPRODUCT(($A$2:$A$8=$D$1)*($C$2:$C$8="V"))

A B C D
Date Name Done 28/06/2007
28/06/2007 Jhon V
28/06/2007 Mark
29/06/2007 Bill V
29/06/2007 Tom V
30/06/2007 Phil
30/06/2007 Andi V
30/06/2007 Din V

My problem is that if i am changing the date in cell A4 from 29/06/2007 to
28/06/2007 the function results is not updating (become 2 instead of 1).
Do anyone knows why??

Thanks in advance

Eli

 

[Toppers]

If you change date for "Bill" (row 4?) to 28/06/07 results is 2 as "Jhon" is
also 28/06/07 , assuming D1= 28/06/07.

OR should D1 be 29/06/07 when the result will change from 2 to 1?

See below

A B C D
Date Name Done 28/06/2007
28/06/2007 Jhon V*
28/06/2007 Mark
28/06/2007 Bill V*
29/06/2007 Tom V
30/06/2007 Phil
30/06/2007 Andi V
30/06/2007 Din V

 

[×לי]

The date for "Bill" should be changed to 28/06/2007. the problem is that
somehow the modification of the cell makes the formula to be blind to it.

Eli

 

[Pete_UK]

Perhaps you have calculation set to manual - press F9 to recalculate
the sheet. Go to Tools | Options | Calculation tab and ensure that you
have it set to Automatic.

Hope this helps.

Pete

 

[×לי]

Unfortunately F9 does nothing and the calculation mode is automatic.
I found out that double click on the relevant cell does recalculate the
sheet, but it is worthless to it manually.

 

[driller]

this maybe a twist on entering real dates against text that looks like a
date, in the cell except in the formula bar...

to offset some difficulties in assuring dependable value of result...i may
try it from now on in this double action manner...

=IF(ISERROR(SUMPRODUCT(((1*($A$2:$A$8))=$D$1)*($C$2:$C$8="V"))),"Check your
Date_Data",SUMPRODUCT((($A$2:$A$8)=$D$1)*($C$2:$C$8="V")))

regards,
driller