Formula to assist with counting dates

[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

 

[Mike H]

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

 

[Jacob Skaria]

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

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

If this post helps click Yes

 

[Jacob Skaria]

Missed out the year

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

If this post helps click Yes

 

[Jacob Skaria]

OR

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

If this post helps click Yes

 

[Jarek Kujawa]

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

 

[Dilly]

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

 

[Dilly]

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))

 

[Jacob Skaria]

Sorry i didnt get you. Could you please explain further

 

[Jacob Skaria]

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