Hi JimDandy,

For last week's completed data:
=SUM(IF((INT((A$1:A$50-2)/7)*7+2=INT((TODAY()-9)/7)*7+2)*(B$1:B$50="Complete
d"),1,))
For last month's completed data:
=SUM(IF((DATE(YEAR(A$1:A$50),MONTH(A$1:A$50),1)=DA TE(YEAR(TODAY()),MONTH(TOD
AY())-1,1))*(B$1:B$50="Completed"),1,))

Both formulae are array-entered. As coded, the test date range is A$1:A$50.
Change the ranges to suit your data, and the 'Completed' string to match the
test string (which could also be a cell reference).


Cheers


"JimDandy" wrote in
message ...

I am trying to count data in one column based on dates in another.
Column A has a series of dates and Column B has one of four different
pieces of data, “New”, “Completed”, “Cancelled”, and “Scheduled”. I am
creating charts based on the counts of these four statuses but I need
to count only those status’ that occurred last month (calendar month,
not last 30 days) and the last week (Last Monday – Friday).

What formulas can filter the data based on calendar months and last
week?


--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile:
http://www.excelforum.com/member.php...o&userid=16578
View this thread: http://www.excelforum.com/showthread...hreadid=533914