Formula to assist with counting dates

  • Thread starter Thread starter Dilly
  • Start date Start date
D

Dilly

Could somebody please assist with a formula to count the number of dates in
February 2009 for example, from a "long" list of random dates.
We have a helpdesk function that logs calls and am trying to establish how
many calls we received in February 2009
 
Hi,

Put this in a cell and drag dwom 11 rows to get a monthly count for each
month of 2009

=SUMPRODUCT(--(MONTH($A$1:$A$30)=ROW(A1)),--(YEAR($A$1:$A$30)=2009))

Change range to suit.

Mike
 
Suppose you have dates in ColA, number of dates in February is .

=SUMPRODUCT(--(MONTH(A1:A1000)=2))

If this post helps click Yes
 
Missed out the year

=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009))

If this post helps click Yes
 
OR

=SUMPRODUCT(--(TEXT(A1:A1000,"mmyyyy")="022009"))

If this post helps click Yes
 
another way:

=SUM(IF((MONTH(A1:A100)=2)*(YEAR(A1:A100)=2009),1,)

this is an array formula so CTRL+SHIFT+ENTER it instead of simply
using ENTER

adjust yr ranges to suit
 
Is it possible to utilise this formula, however, by just highlighting the
column column as the range, as opposed to physically highlighting from
A1-A1000. If i use this folrmula and highlight column A as opposed to
A1:A1000 i get a NUM error

Thanks in advance

Mark
 
Is it possible to utilise the formula below , however, by just highlighting
the
column as the range, as opposed to physically highlighting from
A1-A1000. If i use this folrmula and highlight column A as opposed to
A1:A1000 i get a NUM error

=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009))
 
You will have to remove the headers and other text data to use this formula.
or other wise this will return an error...

If this post helps click Yes
 
Back
Top