I use SUMPRODUCT() to accomplish counting like you're describing. This

function is WAY more flexible than people realize at first. This link has a

wonderful tutorial and the really good stuff is down the sheet a ways past

the "Advantages of Sumproduct"

http://www.xldynamic.com/source/xld....T.html#classic
Read up on that. Then I imagine a formula for count of open items spanning

many months to be something like this:

=SUMPRODUCT(--(RangeOfOpeningDates>=OpeningDate),

--(RangeOfOpeningDates<CutoffOpenDate),

--(RangeOfClosingDates>CutoffOpenDate))

Now, just insert the correct ranges and cell references for those values.

With Sumproduct, you can't refer to whole columns like you can with COUNTIF

unless you're using Excel 2007

--

"Actually, I *am* a rocket scientist." -- JB

Your feedback is appreciated, click YES if this post helped you.

"Nadine" wrote:

> I am attempting to write a COUNTIF statement with greater than and less than

> functionality. I have 2 worksheets.

>

> Sheet 1 contains the extracted data from a database â€“ this cannot be

> changed.

> Row A is a sequential number of the items.

> Row B contains the status (New, In Progress, Closed).

> Row C contains the day and time the item was created.

> Row D contains the day and time the item was closed.

> Cell F2 contains: =MONTH(C2)&"-"&YEAR(C2)

> Cell G2 contains: =IF(B2="Closed", MONTH(C2)&"-"&YEAR(C2),"")

>

> Sheet 2 has the 6 month reporting period as shown below:

> Cell B1 is for the user to enter the MM-YYYY of the first reporting month

> Cell B2 contains the following formula: =DATE(YEAR(B1),MONTH(B1)+C2,DAY(B1))

> to return the MM-YYYY 1 greater than the month in cell B1. This continues

> through B6 so I have 6 months in a row.

> Cell A16 says: =B1 to return the MM-YYYY of the first month for reporting.

> Cell B16 says: =B2 and so on through cell F16

> Row 17 is where I want the results of the COUNTIF formula to begin.

> Cell A17 says: =COUNTIF(Sheet1!$F:$F,Sheet2!A16) to tell me how many were

> opened in the month displayed in cell A16.

> Cell A18 says: =COUNTIF(Sheet1!$G:$G,Sheet2!A16) to tell me how many were

> closed during the month displayed in cell A16.

> Now I have the number of items opened and the number of items closed in each

> month.

>

> What I need to know is how many OPEN items I had at the beginning of the

> current month. Remember that the months in row 16 will changed based on what

> the user enters in cell B2. (This workbook is to be used as a template so it

> will constantly be changing.)

>

> I've tried using the formula for COUNTIF in the help section:

> =COUNTIF(Sheet1!$F:$F,"<Sheet2!A16")-COUNTIF(Sheet1!$G:$G,">Sheet2!B16") to

> tell me how many were open at the beginning (12:01am) of the second month.

> The result was 11 which is incorrect as only 1 item was created in the first

> month and it is still open. So the result should have been 1.

>

> Thanks for any help.

>