| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with SumProduct | Excel Discussion (Misc queries) | |||
| sumproduct problem? | Excel Discussion (Misc queries) | |||
| sumproduct problem | New Users to Excel | |||
| sumproduct problem | Excel Worksheet Functions | |||
| Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
Linear Mode
