Can someone check this formula please?
 

Hi,

I am using the following formula: -

=SUMPRODUCT((Names!$A$2:$A$351="01/03/2006")*(Names!$E$2:$E$3510))

the idea being that it checks A1 to A351 for the date (in this case
01/03/2006) and then returns the total number of occurances.

Problem is ... it don't work!! (Obvious being as I wrote it!!)

Can anyone help, please?

Many thanks

Tony


--
y_not
------------------------------------------------------------------------
y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=541934

Can someone check this formula please?
 

There are several ways to fis but the easiest way, for you, would be to put
your date in a cell and use
=SUMPRODUCT((Names!$A$2:$A$351=b1)*(Names!$E$2:$E$ 3510))

--
Don Guillett
SalesAid Software

"y_not" wrote in
message ...

Hi,

I am using the following formula: -

=SUMPRODUCT((Names!$A$2:$A$351="01/03/2006")*(Names!$E$2:$E$3510))

the idea being that it checks A1 to A351 for the date (in this case
01/03/2006) and then returns the total number of occurances.

Problem is ... it don't work!! (Obvious being as I wrote it!!)

Can anyone help, please?

Many thanks

Tony


--
y_not
------------------------------------------------------------------------
y_not's Profile:
http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=541934

Can someone check this formula please?
 

Try this:
=SUMPRODUCT((Names!$A$2:$A$351=DATE(2006,3,1))*(Na mes!$E$2:$E$3510))

HTH
--
AP

"y_not" a écrit dans le
message de news: ...

Hi,

I am using the following formula: -

=SUMPRODUCT((Names!$A$2:$A$351="01/03/2006")*(Names!$E$2:$E$3510))

the idea being that it checks A1 to A351 for the date (in this case
01/03/2006) and then returns the total number of occurances.

Problem is ... it don't work!! (Obvious being as I wrote it!!)

Can anyone help, please?

Many thanks

Tony


--
y_not
------------------------------------------------------------------------
y_not's Profile:
http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=541934

Can someone check this formula please?
 

Works a treat


--
y_not
------------------------------------------------------------------------
y_not's Profile: http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=541934

Can someone check this formula please?
 

The specific reason why your formula did not work is because "01/03/2006" is
being evaluated as a TEXT string and not a numeric DATE.

Don's suggestion is the best option to use but here are a couple of others:

=SUMPRODUCT((Names!$A$2:$A$351=--"2006/01/03")*(Names!$E$2:$E$3510))

=SUMPRODUCT((Names!$A$2:$A$351=DATE(2006,1,3))*(Na mes!$E$2:$E$3510))

Biff

"y_not" wrote in
message ...

Works a treat


--
y_not
------------------------------------------------------------------------
y_not's Profile:
http://www.excelforum.com/member.php...o&userid=19947
View this thread: http://www.excelforum.com/showthread...hreadid=541934