How to set the function - countif?

E

Eric

Does anyone have any suggestions on how to set the function - countif?
There is a list of date under column A, and there is a given date in cell B1,
I would like to count the date only match the month and day without
concerning the year, and return the number in cell B2.
For example,
Under column A,
11-Dec-2008
13-Jun-2007
14-Apr-2008
14-Apr-2006
The given date is 14-Apr-2003 in cell B1, and it should return 2 in cell B2,
because
14-Apr-2008 and 14-Apr-2006 match the given date 14-Apr-2003 in term of
month and day.
Does anyone have any suggestions on how to set the formula?
Thanks in advance for any suggestions
Eric
 
P

Pecoflyer

Hi
Try =SUMPRODUCT((MONTH(A7:A10)=MONTH(b1))*(DAY(A7:A10)=DAY(b1))

Eric;268242 said:
Does anyone have any suggestions on how to set the function - countif
There is a list of date under column A, and there is a given date i
cell B1
I would like to count the date only match the month and day withou
concerning the year, and return the number in cell B2
For example
Under column A
11-Dec-200
13-Jun-200
14-Apr-200
14-Apr-200
The given date is 14-Apr-2003 in cell B1, and it should return 2 i
cell B2
becaus
14-Apr-2008 and 14-Apr-2006 match the given date 14-Apr-2003 in term o
month and day
Does anyone have any suggestions on how to set the formula
Thanks in advance for any suggestion
Eri


Adapt the range to your needs ( ranges like B:B are not possible in x
versions before 2007

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
E

Eric

Thank everyone very much for suggestions
I receive 0 instead of 2 from following code.
=SUMPRODUCT((MONTH(A1:A100)=MONTH(B1))*(DAY(A1:A100)=DAY(B1)))
What wrong is it?
Thank everyone very much for any suggestions
Eric
 
E

Eric

I need to cover a table, which include blank space "", which cause the error,
but I cannot skip those blank space "".
Does anyone have any suggestions on how to avoid those error because of this
blank space within table from A1 to Z36? and return 2 in cell B2
Thank everyone very much for suggestions
Eric
 
P

Pecoflyer

Things would be easier if you could post a sample of your data
You can do it on our forum ( no strings attached) and you will get
speed
answer
Cheer
Eric;268428 said:
I need to cover a table, which include blank space "", which cause th
error
but I cannot skip those blank space ""
Does anyone have any suggestions on how to avoid those error because o
thi
blank space within table from A1 to Z36? and return 2 in cell B
Thank everyone very much for suggestion
Eri

Things would be easier if you could post a sample of your data
You can do it on our forum ( no strings attached) and you will get
speed
answer
Cheer

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answers

*Adding your XL version* to your post helps finding solution faste
 
E

Eric

Thank everyone very much for suggestions
I don't solve this problem, but replace the character "" with 0, and this
formula is working.
Thank everyone very much
Eric
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top