Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
COUNTIF greater than one column and less than another
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel to COUNTIF if number greater than one but less than another | Excel Worksheet Functions | |||
countif greater/less than argument | Excel Worksheet Functions | |||
Countif cell greater than average | Excel Worksheet Functions | |||
Using COUNTIF to find numbers within a range greater than the mean | Excel Worksheet Functions | |||
countif a date is greater than today | Excel Worksheet Functions |