Results 1 to 2 of 2

Thread: Excel Question

  1. #1
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    876

    Excel Question

    I know this might be more appropriately placed in the 'Programming Discussion' forum but I think it will get more views quicker here and I am in a bit of a hurry.

    I am trying to do the following in excel and cannot work out the formula.

    I have a number of "policies" that are in one of two states: Active or Cancelled. They have a due date of payment and it is either on or beyond that due date or is yet to reach the due date.

    What I need to do is calculate the number of cancelled policies and separate them into two totals: policies that were either cancelled on or beyond the due date, or the total for ones that have not reached the due date.

    So my required columns are: "State", "Date", for instance (that is not the exact naming).

    My current formula, although it does not work, might better demonstrate the thinking. I have attempted to use a COUNTIF and a SUMIF but it doesn't appear to work (because I'm doing it wrong, obviously). I've been chucking AND's in for fun but it wasn't so much fun so I stopped.

    =SUM(IF(AND(PolicyLetter110305!F2:F4204="Cancelled"),(PolicyLetter110305!D2204<G4)+1,0))

    So, effectively want I want to do is check whether the policy in a particular cell is cancelled. If it is then I want to check whether it was before today's date. If it is then I want to add it to a running total of policies that match the same criteria.

    Any ideas?

  2. #2
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    876
    Here is a COUNTIF that works but it doesn't evaluate two criteria:

    =COUNTIF(PolicyLetter110305!F2:F4471,("*"))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •