If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
SUMPRODUCT problem
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 "אלי" wrote: 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 |
#3
|
|||
|
|||
SUMPRODUCT problem
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 "Toppers" wrote: 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 "אלי" wrote: 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 |
#4
|
|||
|
|||
SUMPRODUCT problem
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 On Jun 28, 9:46 am, wrote: 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 "Toppers" wrote: 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 " " wrote: 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- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
SUMPRODUCT problem
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. "Pete_UK" wrote: 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 On Jun 28, 9:46 am, wrote: 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 "Toppers" wrote: 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 " " wrote: 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- Hide quoted text - - Show quoted text - |
#6
|
|||
|
|||
SUMPRODUCT problem
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 -- ***** birds of the same feather flock together.. "אלי" wrote: 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. "Pete_UK" wrote: 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 On Jun 28, 9:46 am, wrote: 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 "Toppers" wrote: 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 " " wrote: 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- Hide quoted text - - Show quoted text - |
Thread Tools | |
Display Modes | |
|
|