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

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


Eric;268242 Wrote:
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



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


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74851

=SUMPRODUCT((MONTH(A1:A100)=MONTH(B1))*(DAY(A1:A10 0)=DAY(B1)))

SUMPRODUCT() should always be considered if you are using more than one
criteria.

See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200838


"Eric" wrote:

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

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

"Gary''s Student" wrote:

=SUMPRODUCT((MONTH(A1:A100)=MONTH(B1))*(DAY(A1:A10 0)=DAY(B1)))

SUMPRODUCT() should always be considered if you are using more than one
criteria.

See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200838


"Eric" wrote:

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

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

"Eric" wrote:

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

"Gary''s Student" wrote:

=SUMPRODUCT((MONTH(A1:A100)=MONTH(B1))*(DAY(A1:A10 0)=DAY(B1)))

SUMPRODUCT() should always be considered if you are using more than one
criteria.

See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200838


"Eric" wrote:

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

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 a
speedy
answer.
Cheers
Eric;268428 Wrote:
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

"Eric" wrote:

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

"Gary''s Student" wrote:

=SUMPRODUCT((MONTH(A1:A100)=MONTH(B1))*(DAY(A1:A10 0)=DAY(B1)))

SUMPRODUCT() should always be considered if you are using more than
one
criteria.

See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200838


"Eric" wrote:

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


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 a
speedy
answer.
Cheers


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74851

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

"Pecoflyer" wrote:


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 a
speedy
answer.
Cheers
Eric;268428 Wrote:
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

"Eric" wrote:

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

"Gary''s Student" wrote:

=SUMPRODUCT((MONTH(A1:A100)=MONTH(B1))*(DAY(A1:A10 0)=DAY(B1)))

SUMPRODUCT() should always be considered if you are using more than
one
criteria.

See:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
Gary''s Student - gsnu200838


"Eric" wrote:

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


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 a
speedy
answer.
Cheers


--
Pecoflyer

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

*Adding your XL version* to your post helps finding solution faster
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=74851