Count data based on past dates
 

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

Count data based on past dates
 

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

Count data based on past dates
 

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

Count data based on past dates
 

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

Count data based on past dates
 

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

Count data based on past dates
 

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

Count data based on past dates
 

=SUMPRODUCT(--(A2:A50=C1),--(A2:A50<+D1),--(B2:B50="Complete"))

where C1 is start date and D1 end date, adapt the ranges to fit your own
data

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"JimDandy" wrote in
message ...

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

Count data based on past dates
 

Thanks, that was it and I appreciate the quick reply…Now, can ask for
help with the next problem...?

Using the same data, I need to segment out only those records for the
counts by type. Initially all my record names began with CR but now I
have records that begin IMAC. How do I perform a count of all records
given the need to separate out only those that begin with CR and ignore
the rest? In this example, I would expect to see a count of two “New” CR
records, which ignores the one "New" IMAC record. Hopefully, I can apply
the logic here to all of the above scenarios where I count records by
this week, this month, etc...


Code:
--------------------

Name Date Status
CR-001 6/1/2006 New
CR-002 6/2/2006 New
CR-003 6/3/2006 Approved
CR-004 6/4/2006 Approved
CR-005 6/5/2006 Completed
CR-006 6/6/2006 Completed
CR-007 6/7/2006 Cancelled
CR-008 6/8/2006 Completed
CR-009 6/9/2006 Approved
CR-010 6/10/2006 Approved
IMAC-001 6/11/2006 New
IMAC-002 6/12/2006 Completed

--------------------


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