Counting specific dates

[gooders]

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.

 

[Per Jessen]

Hi

Try this one:

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

Regards,
Per

 

[מיכ×ל (מיקי) ×בידן]

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

 

[gooders]

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]

=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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[gooders]

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

 

[gooders]

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

 

[David Biddulph]

Why the N() function ?
--
David Biddulph

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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[מיכ×ל (מיקי) ×בידן]

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$40)=2010)) will do
*** Please note the Multiplication instead your comma ***
Micky
--
והמשך/×™, × ×, ×œ×§×¨×•× ×ת השורה הב××”:
***********
×× ×ª×’×•×‘×ª×™ עזרה לחץ/×™, × ×, על <כן> בפס ×”×ופקי התחתון!
***********
מיכ×ל ×בידן
מנהל ×¤×•×¨×•× "×ופיס" ב"תפוז"
[Microsoft" Most Valuable Professional [MVP"