Hi, this seems to be a very simple query but obviously too hard for my brain.
I have a column full of dates i.e.22/11/2009, 1/12/2009 etc. I may have
duplicates which isn't a problem. What I want to do is count the number of
rows that say were done in the month of November etc. I use Excel 2003.

Many thanks for any assistance.

Hi

Try this one:

=COUNTIF(A1:A10,"=01/11/2009")-COUNTIF(A1:A10,"30/11/2009")

Regards,
Per

"gooders" skrev i meddelelsen
...
Hi, this seems to be a very simple query but obviously too hard for my
brain.
I have a column full of dates i.e.22/11/2009, 1/12/2009 etc. I may have
duplicates which isn't a problem. What I want to do is count the number of
rows that say were done in the month of November etc. I use Excel 2003.

Many thanks for any assistance.

Try: =SUMPRODUCT(N(MONTH(A1:A40)=11))
where A1:A40 holds the different dates
Micky

"gooders" wrote:

Hi, this seems to be a very simple query but obviously too hard for my brain.
I have a column full of dates i.e.22/11/2009, 1/12/2009 etc. I may have
duplicates which isn't a problem. What I want to do is count the number of
rows that say were done in the month of November etc. I use Excel 2003.

Many thanks for any assistance.

Sorry further to above I know that I can use the following:

=COUNT(IF(MONTH(A1:A100)=11,1,"")) as an array formula but I want to be able
to differentiate also between years, i.e. November 2009 and November 2010 etc.

=sumproduct(--(text(a1:a100,"yyyymm")="200911"))

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

gooders wrote:

Sorry further to above I know that I can use the following:

=COUNT(IF(MONTH(A1:A100)=11,1,"")) as an array formula but I want to be able
to differentiate also between years, i.e. November 2009 and November 2010 etc.

--

Dave Peterson

Following the principal from above:
=SUMPRODUCT(N(MONTH(A1:A40)=11)*(YEAR(A1:A40)=2009 ))
Micky


"gooders" wrote:

Sorry further to above I know that I can use the following:

=COUNT(IF(MONTH(A1:A100)=11,1,"")) as an array formula but I want to be able
to differentiate also between years, i.e. November 2009 and November 2010 etc.

That's great Mickey, many thanks, better than my formula, but now I want to
take it a step further and differentiate between months and years, i.e. count
all the months with a certain year.

Lesley

"מיכ�ל (מיקי) �בידן" wrote:

Try: =SUMPRODUCT(N(MONTH(A1:A40)=11))
where A1:A40 holds the different dates
Micky

"gooders" wrote:

Hi, this seems to be a very simple query but obviously too hard for my brain.
I have a column full of dates i.e.22/11/2009, 1/12/2009 etc. I may have
duplicates which isn't a problem. What I want to do is count the number of
rows that say were done in the month of November etc. I use Excel 2003.

Many thanks for any assistance.

I think I've got it using Mickey's formula:

=SUMPRODUCT(N(MONTH($A$1:$A$40)=12),N(YEAR($A$1:$A $40)=2010))

Thanks

"gooders" wrote:

Hi, this seems to be a very simple query but obviously too hard for my brain.
I have a column full of dates i.e.22/11/2009, 1/12/2009 etc. I may have
duplicates which isn't a problem. What I want to do is count the number of
rows that say were done in the month of November etc. I use Excel 2003.

Many thanks for any assistance.

Why the N() function ?
--
David Biddulph

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Following the principal from above:
=SUMPRODUCT(N(MONTH(A1:A40)=11)*(YEAR(A1:A40)=2009 ))
Micky


"gooders" wrote:

Sorry further to above I know that I can use the following:

=COUNT(IF(MONTH(A1:A100)=11,1,"")) as an array formula but I want to be
able
to differentiate also between years, i.e. November 2009 and November 2010
etc.

Replace MONTH with YEAR and 11 with 2009
=SUMPRODUCT(N(YEAR(A1:A40)=2009))
Micky


"gooders" wrote:


That's great Mickey, many thanks, better than my formula, but now I want to
take it a step further and differentiate between months and years, i.e. count
all the months with a certain year.

Lesley

"מיכ�ל (מיקי) �בידן" wrote:

Try: =SUMPRODUCT(N(MONTH(A1:A40)=11))
where A1:A40 holds the different dates
Micky

"gooders" wrote:

Hi, this seems to be a very simple query but obviously too hard for my brain.
I have a column full of dates i.e.22/11/2009, 1/12/2009 etc. I may have
duplicates which isn't a problem. What I want to do is count the number of
rows that say were done in the month of November etc. I use Excel 2003.

Many thanks for any assistance.

Please note that if you have more(!) than one segment using SUMPRODUCT - you
can omit all the "N"s.
SUMPRODUCT cannot calculate on TRUEs and FALSEs
However, if two, or more segments, of T & F are multiplied - the result is
always 0 or 1 which is very welcome by the function.
So: =SUMPRODUCT((MONTH($A$1:$A$40)=12)*(YEAR($A$1:$A$4 0)=2010)) will do
*** Please note the Multiplication instead your comma ***
Micky
--
והמשך/×™, ×*×�, לקרו×� ×�ת השורה הב×�×”:
***********
×�×� תגובתי עזרה לחץ/×™, ×*×�, על <כן בפס ×”×�ופקי התחתון!
***********
מיכ�ל �בידן
מ×*הל פורו×� "×�ופיס" ב"תפוז"
[Microsoft" Most Valuable Professional [MVP"


"gooders" wrote:

I think I've got it using Mickey's formula:

=SUMPRODUCT(N(MONTH($A$1:$A$40)=12),N(YEAR($A$1:$A $40)=2010))

Thanks

"gooders" wrote:

Hi, this seems to be a very simple query but obviously too hard for my brain.
I have a column full of dates i.e.22/11/2009, 1/12/2009 etc. I may have
duplicates which isn't a problem. What I want to do is count the number of
rows that say were done in the month of November etc. I use Excel 2003.

Many thanks for any assistance.