Using Countif with dates

[bjg1]

We've created a workbook in Excel 2003 for the boss. We have a query to download our trouble tickets from SQL. We want to track the daily new tickets and closed tickets. We currently are using the COUNTIF function for each day to get the number of tickets Opened and another calculation for the number Closed. We then subtract these numbers for the day to get the trend.

We started with the number of tickets open as of 1/1/2011. Daily we add/subtract the trend amount, which in theory should balance to our actual number of tickets open. Doesn't work. Currently we are off by 10.

The current formula is COUNTIF(Query!E:E,a2). Count the Open date column if the date equals what is in cell A1. There is a similar formula for Closed date. The daily numbers produced by these calculations seem to balance with the database itself.

One question is why would the formula to keep track of the current number of tickets not work

Another question is what is the format of the COUNTIF function if we want to get the count of tickets opened before 1/1/2011? We've tried COUNTIF(Query!E:E, "<01/01/2011"). We even tried using the number that Excel uses for the date (40544). We either get 0 or the total number of tickets. Is this issue because the information is downloaded from SQL and therefore the empty fields (no closed date) are causing issues?

[sdckapr]

COUNTIF(Query!E:E,a2) will find an exact match. If the value in the cell has a time component it will not match exactly. If the value is not a number it will not macch.

COUNTIF(Query!E:E, "<01/01/2011") or COUNTIF(query!E:E, "<40544") should both work: they work for me in my tests...

Could you post an example file with proprietary info removed that demonstrates the problem?

Steve

[bjg1]

When I use the COUNTIF(Query!E:E,"<40544"), I get the total number of records on the "2011" worksheet, not just the record less that the date.

[sdckapr]

There are 2 issues. Your formula is:
=COUNTIF(Query!E:E,"<K1")
This literally looks for the values to be less than the literal string "K1". You want to look at less than what is in the cell K1 so you must use:
=COUNTIF(E:E,"<"&K1)

The second problem is that the cells in Query!E:E do NOT contain numbers, they contain text that looks like numbers. To convert them to numbers:
select a blank cell
edit -copy
select the range of date-numbers (Cols D&E)
Edit - Paste special - ADD [OK]

They will be numbers, Format the cells as desired.

Steve

[bjg1]

The second problem is probably something we cannot change. We download the data daily, which would require that we convert the data to numbers after each download. We will test to see if it is worth the change.

I don't know if this solves or explains the issue with the daily count being off by 10.

[sdckapr]

If you can't change the 2nd problem I think you will have problems if you ever want to use countif especially with greater and less than. I am not sure it is possible to tell the countif to look for a literal string that looks like a number.

I am not sure exactly what the problem is with daily counts, but it would not surprise me if it were related to this in some way. I am not sure what number is off by 10 in the workbook. Could you elaborate?

Steve

[tfspry]

Will these work ......

For dates less than 2011-01-01 the answer is 887
=COUNTIF(Query!E:E,"<2011-01-01""")

For dates greater than or equal to 2011-01-01 the answer is 101
=COUNTIF(Query!E:E,">=2011-01-01""")-1

The total of the two formulas is 988.

[sdckapr]

tfspry method will NOT work correctly if the data set had the a cell with a date of 2011-01-01 in it. This value would be counted as less than the lookup, not equal to it as desired due to the extra character that is added.

The formula works since adding the extra quotation mark (it does not have to be a quotation mark, any character will work the same way), it forces XL to consider the number text.

To do this type of countif with K1:
Change the formula in K1 to display the date as text in the correct format:
=TEXT(A2,"yyyy-mm-dd")&Char(1)

Then you can use:
=COUNTIF(Query!E:E,"<="&K1)

[tfspry does not mention the reason, but the "-1" in the 2nd formula is to not count the Header value from row 1. The text "RecvdDate" is > than the text phrase 2011-01-01" so is counted and must be removed from the total.]

Steve

[tfspry]

If you can't change the 2nd problem I think you will have problems if you ever want to use countif especially with greater and less than. I am not sure it is possible to tell the countif to look for a literal string that looks like a number.......

My formula was offering an alternative that does give the same answer as Steve's new post.

My formula eliminated the need for the previously proposed solution of ...
"The second problem is that the cells in Query!E:E do NOT contain numbers, they contain text that looks like numbers. To convert them to numbers:
select a blank cell
edit -copy
select the range of date-numbers (Cols D&E)
Edit - Paste special - ADD [OK]
They will be numbers, Format the cells as desired."

My alternative was just offered to avoid the above steps. It also avoids the need to refer to cell K1 and creating a new formula in cell K1. It also gives the option to type in the date of your choosing within the COUNTIF formula.

[rory]

You can also use SUMPRODUCT:
=SUMPRODUCT(--(Query!E2:E989+0<K1))

Note that pre-2007 you cannot pass an entire column to SUMPRODUCT so you need to specify a range (though you can use a dynamic named range if required)