how to count matching cells?
 

i want a formula to if a cell date is 07/05/05 and another cell contains a
date count.

this is for my newsletter sheet where each mail out has a sent date and then
replies are logged in the next cell if any, so i want to calculate the number
of replies recieved from my mailing on particular days

any hlp would be helpful

Have a look in HELP index for SUMIF

--
Don Guillett
SalesAid Software

"gary dickinson" <gary wrote in message
...
i want a formula to if a cell date is 07/05/05 and another cell contains a
date count.

this is for my newsletter sheet where each mail out has a sent date and
then
replies are logged in the next cell if any, so i want to calculate the
number
of replies recieved from my mailing on particular days

any hlp would be helpful

One way

=SUMPRODUCT(--(A2:A20=DATE(2005,7,5)),--(ISNUMBER(B2:B20)))

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in message
...
i want a formula to if a cell date is 07/05/05 and another cell contains a
date count.

this is for my newsletter sheet where each mail out has a sent date and
then
replies are logged in the next cell if any, so i want to calculate the
number
of replies recieved from my mailing on particular days

any hlp would be helpful

Sorry guys, i still cant make it work, i give an example below:


a b
1 27/04/05 27/04/05
2 27/04/05 28/04/05
3 27/04/05
4 28/04/05 29/04/05
5 28/04/05
6 28/04/05

my formula wants to say if a cell column a = 27/04/05 how is there a date in
the same row in column b, then i do the same for the next date 28/04/05.

so my answer should be 27/04/05 = 2 replies
and 28/04/05 = 1 repliy

my sheet contains some 18000 rows so can i use A:A?

sorry if i am a little slow on the uptake here.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A20=DATE(2005,7,5)),--(ISNUMBER(B2:B20)))

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in message
...
i want a formula to if a cell date is 07/05/05 and another cell contains a
date count.

this is for my newsletter sheet where each mail out has a sent date and
then
replies are logged in the next cell if any, so i want to calculate the
number
of replies recieved from my mailing on particular days

any hlp would be helpful

You cannot use the whole column A:A, what answer do you get with

=SUMPRODUCT(--(A2:A1800=DATE(2005,4,27)),--(ISNUMBER(B2:B1800)))

if you indeed have as in your example you should get 2, however if the dates
are text you would get zero,
assume A2 holds 27/04/05 what is the answer you get with =ISTEXT(A2)

if TRUE you have text dates

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in message
...
Sorry guys, i still cant make it work, i give an example below:


a b
1 27/04/05 27/04/05
2 27/04/05 28/04/05
3 27/04/05
4 28/04/05 29/04/05
5 28/04/05
6 28/04/05

my formula wants to say if a cell column a = 27/04/05 how is there a date
in
the same row in column b, then i do the same for the next date 28/04/05.

so my answer should be 27/04/05 = 2 replies
and 28/04/05 = 1 repliy

my sheet contains some 18000 rows so can i use A:A?

sorry if i am a little slow on the uptake here.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A20=DATE(2005,7,5)),--(ISNUMBER(B2:B20)))

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in
message
...
i want a formula to if a cell date is 07/05/05 and another cell contains
a
date count.

this is for my newsletter sheet where each mail out has a sent date and
then
replies are logged in the next cell if any, so i want to calculate the
number
of replies recieved from my mailing on particular days

any hlp would be helpful

Thanks Peo

That worked great !!!!!!!

"Peo Sjoblom" wrote:

You cannot use the whole column A:A, what answer do you get with

=SUMPRODUCT(--(A2:A1800=DATE(2005,4,27)),--(ISNUMBER(B2:B1800)))

if you indeed have as in your example you should get 2, however if the dates
are text you would get zero,
assume A2 holds 27/04/05 what is the answer you get with =ISTEXT(A2)

if TRUE you have text dates

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in message
...
Sorry guys, i still cant make it work, i give an example below:


a b
1 27/04/05 27/04/05
2 27/04/05 28/04/05
3 27/04/05
4 28/04/05 29/04/05
5 28/04/05
6 28/04/05

my formula wants to say if a cell column a = 27/04/05 how is there a date
in
the same row in column b, then i do the same for the next date 28/04/05.

so my answer should be 27/04/05 = 2 replies
and 28/04/05 = 1 repliy

my sheet contains some 18000 rows so can i use A:A?

sorry if i am a little slow on the uptake here.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A20=DATE(2005,7,5)),--(ISNUMBER(B2:B20)))

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in
message
...
i want a formula to if a cell date is 07/05/05 and another cell contains
a
date count.

this is for my newsletter sheet where each mail out has a sent date and
then
replies are logged in the next cell if any, so i want to calculate the
number
of replies recieved from my mailing on particular days

any hlp would be helpful

Thanks for the feedback

--
Regards,

Peo Sjoblom


"gary dickinson" wrote in message
...
Thanks Peo

That worked great !!!!!!!

"Peo Sjoblom" wrote:

You cannot use the whole column A:A, what answer do you get with

=SUMPRODUCT(--(A2:A1800=DATE(2005,4,27)),--(ISNUMBER(B2:B1800)))

if you indeed have as in your example you should get 2, however if the
dates
are text you would get zero,
assume A2 holds 27/04/05 what is the answer you get with =ISTEXT(A2)

if TRUE you have text dates

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in
message
...
Sorry guys, i still cant make it work, i give an example below:


a b
1 27/04/05 27/04/05
2 27/04/05 28/04/05
3 27/04/05
4 28/04/05 29/04/05
5 28/04/05
6 28/04/05

my formula wants to say if a cell column a = 27/04/05 how is there a
date
in
the same row in column b, then i do the same for the next date
28/04/05.

so my answer should be 27/04/05 = 2 replies
and 28/04/05 = 1 repliy

my sheet contains some 18000 rows so can i use A:A?

sorry if i am a little slow on the uptake here.

"Peo Sjoblom" wrote:

One way

=SUMPRODUCT(--(A2:A20=DATE(2005,7,5)),--(ISNUMBER(B2:B20)))

--
Regards,

Peo Sjoblom


"gary dickinson" <gary wrote in
message
...
i want a formula to if a cell date is 07/05/05 and another cell
contains
a
date count.

this is for my newsletter sheet where each mail out has a sent date
and
then
replies are logged in the next cell if any, so i want to calculate
the
number
of replies recieved from my mailing on particular days

any hlp would be helpful