software

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: www.excelforum.com/member.php...oamp;userid=16578
View this thread: www.excelforum.com/showthread...hreadid=533914Hi 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=quot;Complete
dquot;),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=quot;Completedquot;),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).Cheersquot;JimDandyquot; gt; wrote in
message ...
gt;
gt; I am trying to count data in one column based on dates in another.
gt; Column A has a series of dates and Column B has one of four different
gt; pieces of data, “New”, “Completed”, “Cancelled”, and “Scheduled”. I am
gt; creating charts based on the counts of these four statuses but I need
gt; to count only those status’ that occurred last month (calendar month,
gt; not last 30 days) and the last week (Last Monday – Friday).
gt;
gt; What formulas can filter the data based on calendar months and last
gt; week?
gt;
gt;
gt; --
gt; JimDandy
gt; ------------------------------------------------------------------------
gt; JimDandy's Profile:
www.excelforum.com/member.php...oamp;userid=16578
gt; View this thread: www.excelforum.com/showthread...hreadid=533914
gt;

Great help! Thanks....--
JimDandy
------------------------------------------------------------------------
JimDandy's Profile: www.excelforum.com/member.php...oamp;userid=16578
View this thread: 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: www.excelforum.com/member.php...oamp;userid=16578
View this thread: www.excelforum.com/showthread...hreadid=533914Hi Jim,

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

Cheers

quot;JimDandyquot; gt; wrote in
message ...
gt;
gt; The solution was sweet but I have one twist, if I may.
gt;
gt; The month formula does the job of limiting the count to items for the
gt; previous month, keeping all of March numbers together, for instance,
gt; but the formula for counting the items from the previous week does this
gt; a little differently. It counts the items from the 7 day period prior to
gt; the 7 day period I'm in today. So, given that today is Friday, I get the
gt; data from Friday a week ago back 7 days.
gt;
gt; What I'd like to do is count the number of items in the last 7 day
gt; period from the Sunday thru Saturday of the previous week. This way the
gt; previous weeks number would remain constant no matter what day of this
gt; week I show the data.
gt;
gt;
gt; --
gt; JimDandy
gt; ------------------------------------------------------------------------
gt; JimDandy's Profile:
www.excelforum.com/member.php...oamp;userid=16578
gt; View this thread: www.excelforum.com/showthread...hreadid=533914
gt;

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: www.excelforum.com/member.php...oamp;userid=16578
View this thread: www.excelforum.com/showthread...hreadid=533914=SUMPRODUCT(--(A2:A50gt;=C1),--(A2:A50lt; D1),--(B2:B50=quot;Completequot;))

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
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;JimDandyquot; gt; wrote in
message ...
gt;
gt; These were superb answers and extremely helpful. I'd like to add a new
gt; condition and see if I can get it solved too.
gt;
gt; Along the same lines as above, I need to count the number of times a
gt; value appears in one row based on dates in another. Using the same
gt; example above, I'd like to count all the times the value in Column B
gt; equals quot;Completequot; when the date in Column A is between two dates, which
gt; I'll capture in Cells C1 and D1
gt;
gt;
gt; --
gt; JimDandy
gt; ------------------------------------------------------------------------
gt; JimDandy's Profile:
gt; www.excelforum.com/member.php...oamp;userid=16578
gt; View this thread: www.excelforum.com/showthread...hreadid=533914
gt;

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 quot;Newquot; 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-0016/1/2006New
CR-0026/2/2006New
CR-0036/3/2006Approved
CR-0046/4/2006Approved
CR-0056/5/2006Completed
CR-0066/6/2006Completed
CR-0076/7/2006Cancelled
CR-0086/8/2006Completed
CR-0096/9/2006Approved
CR-0106/10/2006Approved
IMAC-0016/11/2006New
IMAC-0026/12/2006Completed

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