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.
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.