Count data based on past dates

[JimDandy]

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?

 

[macropod]

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)=DATE(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]

Great help! Thanks....

 

[JimDandy]

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 th
previous month, keeping all of March numbers together, for instance
but the formula for counting the items from the previous week does thi
a little differently. It counts the items from the 7 day period prior t
the 7 day period I'm in today. So, given that today is Friday, I get th
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 da
period from the Sunday thru Saturday of the previous week. This way th
previous weeks number would remain constant no matter what day of thi
week I show the data

 

[macropod]

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]

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

 

[Peo Sjoblom]

=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]

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

Using the same data, I need to segment out only those records for th
counts by type. Initially all my record names began with CR but now
have records that begin IMAC. How do I perform a count of all record
given the need to separate out only those that begin with CR and ignor
the rest? In this example, I would expect to see a count of two “New” C
records, which ignores the one "New" IMAC record. Hopefully, I can appl
the logic here to all of the above scenarios where I count records b
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