Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions | |||
Can I count how many grey-filled cells are in a row ? | Excel Worksheet Functions | |||
Count non-colored cells | Excel Discussion (Misc queries) | |||
Count number of shaded cells | Excel Discussion (Misc queries) |