Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Will Not Find Date
Does the SUMPRODUCT function find dates? An example of my formula,as follows, will not work i.e.returns "0": =SUMPRODUCT(($A$1:$A$200="26/10/2004)*($B1$B200=$C1)) Please let me know if when using date references I have to do something extra? Thanks Andy -- andyp161 ------------------------------------------------------------------------ andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654 View this thread: http://www.excelforum.com/showthread...hreadid=506132 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Will Not Find Date
Hi Andy
You are missing a double quote at the end of your date. You also need to coerce this text to be a date value, by preceding with the double unary minus so, =SUMPRODUCT(($A$1:$A$200=--"26/10/2004")*($B1$B200=$C1)) Personally, I always use the DATE() function =SUMPRODUCT(($A$1:$A$200=DATE(2004,10,26))*($B1$B2 00=$C1)) -- Regards Roger Govier "andyp161" wrote in message ... Does the SUMPRODUCT function find dates? An example of my formula,as follows, will not work i.e.returns "0": =SUMPRODUCT(($A$1:$A$200="26/10/2004)*($B1$B200=$C1)) Please let me know if when using date references I have to do something extra? Thanks Andy -- andyp161 ------------------------------------------------------------------------ andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654 View this thread: http://www.excelforum.com/showthread...hreadid=506132 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Will Not Find Date
=SUMPRODUCT(($A$1:$A$200=--"2004-10-26")*($B1:$B200=$C1))
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "andyp161" wrote in message ... Does the SUMPRODUCT function find dates? An example of my formula,as follows, will not work i.e.returns "0": =SUMPRODUCT(($A$1:$A$200="26/10/2004)*($B1$B200=$C1)) Please let me know if when using date references I have to do something extra? Thanks Andy -- andyp161 ------------------------------------------------------------------------ andyp161's Profile: http://www.excelforum.com/member.php...o&userid=11654 View this thread: http://www.excelforum.com/showthread...hreadid=506132 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
Function to find row with most current date | Excel Worksheet Functions | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
find date in Col A corresponding to min value in Col B | Excel Worksheet Functions |