I have the last formula that works great, but lets say this

I have a date in one colm / and a type in another colm



date / hearing type
01/02/09 / rh
01/02/09 / prelim
06/02/09 / rh
05/30/09 / prelim

that it will only count the dates that are not past due.

so in the case it would only count 1 rh and 1 prelim cause there are only 2 that have dates that have not arrive yet. and 2 that are past due. I just want it to count the ones that have not arrive.

By Crickey Tinkertron - you REALLY need to turn this into a database!
This one took me and another collegue ages to work out a form of solution - whereas both of us said that it would take a minute to set up in Access!!

OK - here goes (anyone out there with a simpler, cleaner solution, PLEASE, jump right in):

You'll need to:

• Create a new column (for this example, I'll call it Column C, and it will be to the right of your Hearing Type Column) (it can be hidden after you have finished setting it up!)
• In the top cell add the formula
  Code:

  =Today()

• This will make the Cell Value equal to Today's Date
• In the column (eg Column C) next to your Date (eg Column A) and Hearing Type (eg Column B) we need to enter a formula that will do the following:

1. Look in Column A and make sure it is a later Date than in Cell C1
2. Look in Column B and find the different Hearing Type info
3. If both values are true, then return a code number for each of the different Hearing Type options (this is so we can use that COUNTIF function again to return the value to your second sheet)

• Here is the unedited formula
  Code:

  =IF(AND(A2>=$C$1,B2="rh"),"1",IF(AND(B2="prelim",A2>=$C$1),"2","0"))

• It's and embedded If function, using an AND function to make the logical test dependant on BOTH results being positive. IF(*logical test* (does it find a date greater than today in column A and the "rh" code in Column B),*If YES*, put the number 1 into the cell, ,*If NO*), go to the next IF Statement) - Normally the *If NO* would place another value in the cell
  Code:

  =IF(A1="dog","There is a DOG","The DOG was a figment of my imagination")

• As you can see in the unedited formula above, I have, instead, added another If formula - this can be done up to 7 times in MSO 2003 (So I hope you don't need more than 7 hearing types). All you do, is start the next If Statement instead of the *If NO*

The idea here is to return a "0" if the date is befor today's date, and the numbers 1-7 for each hearing type where the date is equal to, or greater than the date in Cell C1.

As long as you have made the Cell reference to C1 Absolute ($C$1) then you can drag the formula down Column C.

When you have finished setting this column up, you can hide it if you wish.


NEXT
On your second sheet, you can now use the COUNTIF function from before, but modified to look for the Code Number in Column C relating to the hearing type.

WoW, that's a mouth full. I trying to read and understand the "programming formula" and now I know why I don't. LOL. Can there be a overkilling when doing formula database? I keep coming up with ideas, and there got to be a stopping point, isnt there? I really want to get with you and explain what I do and what i'm trying to get done with this database. Maybe this weekend.