Excel: Count all records that fall in a date range 1

[vivasuzi]

I am having trouble with this and it's driving me nuts. I have a table (don't we all):

record record info record date
----------------------------------
blah blahblah 4/30/05
blah blahblah 5/30/05
blah blahblah 6/1/05
blah blahblah 7/30/05

I have one cell (E1) that shows todays date (Today() - currently it is 5/4/05)

I have a cell next to it with this formula:

=COUNTIF(D6127,"<=E1")

That works, and gives me the right count of dates that have passed. In the above example it would give me "1"

Then I have a cell (E2) that shows todays date + 30 (Today()+30 - currently it is 6/3/05)

So I thought I could simply make this formula to calculate the dates between 6/3/05 and 5/4/05:

=COUNTIF(D6127,"<=E2")-COUNTIF(D6127,"<E1")

In the above example that would be "3-1" or "2"

However, it KEEPS GIVING me zero even though I KNOW there are 2 dates in the range. If I change E2 to 6/3/05 in the formula, I get the correct answer. However I want this to be dynamic. Does anyone know what is going wrong? I have the same range (E1-E2) in my conditional formatting and it works to change those 2 cells to yellow. Why won't this work in the calculation?

Any help/suggestions is appreciated. I really hope I made some stupid mistake b/c that way I know I'm not crazy :-D Thanks!

*Suzanne*

http://www.vivasuzi.com

 

[AnotherHiggins]

vivasuzi,

There's a slight problem in your syntax. Try deleting all of the dates in column D.... You'll still get an answer of 1 for your first formula! That's because putting [COLOR=blue white]"<=E2"[/color] in your formula makes excel look for text. You get a result of 1 because there is one item in column D that is text and starts with a 'lower' letter than E alphabetically.

In case I didn't explain that well, try this - in column D, type the following and watch the result of your first formula change: a, b, c, d, e, f, g, h, etc.

Get it?

Now, to work around this, change your first formula to this:
[COLOR=blue white]=COUNTIF(D,"<="&E1)[/color]
following the same logic, your second formula will become:
[COLOR=blue white]=COUNTIF(D,"<="&E2)-COUNTIF(D,"<"&E1)
[/color]

Also notice that I've excluded the row numbers from column D. You can put them back in if they are needed, but usually you're safe just using the entire column in the formula.


[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[vivasuzi]

The formula COUNTIF(D,"<=E2") actually gives me 22 right now. Which is the same answer I get for "<=E1".

There are 22 entries less than the current date, and 24 entries less than today's date+30. Using the "insert formula" thing to see what is happening, it says the value of the first equation is 22, making the value of the formula 22-22 or 0. Also, the fields in D were dates, so it wasn't counting text fields because there were no text fields in the range at all.

I did change to the & signs and it is working now, however I still don't see why it was adding up to 22 for each formula. I don't know where it got the # from if it wasn't adding the dates.

Also, I have to put in the ranges because some cells above and below the range are not to be included. Usually it is not safe for me to select an entire column.

*Suzanne*

http://www.vivasuzi.com