Counting dates with specific criteria

[Steve]

Hi, I have a column with various dates, I need some formulae to count
different criteria.

The criteria are: 1. Dates in the current month. 2. Dates last month. 3.
dates in a calender year. 4. Dates in a financial year.
Each criteria will be shown in seperate cells on a different worksheet on
Excel 2003, also the current month is always the current month as we progress
through the year.
Thanks in advance,

 

[T. Valko]

Try these...

1. Dates in the current month.

=SUMPRODUCT(--(TEXT(A2:A21,"myyyy")=TEXT(NOW(),"myyyy")))

2. Dates last month.

=SUMPRODUCT(--(TEXT(A2:A21,"myyyy")=TEXT(TODAY()-DAY(NOW()),"myyyy")))

3. Dates in a calender year. Where n = the year number

=SUMPRODUCT(--(YEAR(A2:A21)=n))

4. Dates in a financial year.

C2 = the start date of your financial year
D2 = the end date of your financial year

=SUMPRODUCT(--(A2:A21>=C2),--(A2:A21<=D2))