Counting specific dates

  • Thread starter Thread starter gooders
  • Start date Start date
G

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.
 
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
 
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.
 
Following the principal from above:
=SUMPRODUCT(N(MONTH(A1:A40)=11)*(YEAR(A1:A40)=2009))
Micky
 
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
 
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
 
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"
 
Back
Top