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

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

Great help! Thanks....


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

The solution was sweet but I have one twist, if I may.

The month formula does the job of limiting the count to items for the
previous month, keeping all of March numbers together, for instance,
but the formula for counting the items from the previous week does this
a little differently. It counts the items from the 7 day period prior to
the 7 day period I'm in today. So, given that today is Friday, I get the
data from Friday a week ago back 7 days.

What I'd like to do is count the number of items in the last 7 day
period from the Sunday thru Saturday of the previous week. This way the
previous weeks number would remain constant no matter what day of this
week I show the data.


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

Hi Jim,

Try:
=SUM(IF((INT((A$1:A$50-1)/7)*7+1=INT((TODAY()-8)/7)*7+1)*(B$1:B$50="Complete
d"),1,))
array-entered as before. If this formula calculates on a Saturday, it'll
give all of this week's results.

Cheers

"JimDandy" wrote in
message ...

The solution was sweet but I have one twist, if I may.

The month formula does the job of limiting the count to items for the
previous month, keeping all of March numbers together, for instance,
but the formula for counting the items from the previous week does this
a little differently. It counts the items from the 7 day period prior to
the 7 day period I'm in today. So, given that today is Friday, I get the
data from Friday a week ago back 7 days.

What I'd like to do is count the number of items in the last 7 day
period from the Sunday thru Saturday of the previous week. This way the
previous weeks number would remain constant no matter what day of this
week I show the data.


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

These were superb answers and extremely helpful. I’d like to add a new
condition and see if I can get it solved too.

Along the same lines as above, I need to count the number of times a
value appears in one row based on dates in another. Using the same
example above, I’d like to count all the times the value in Column B
equals “Complete” when the date in Column A is between two dates, which
I’ll capture in Cells C1 and D1


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