+ Reply to Thread
Results 1 to 1 of 1

need formula to count number of rows based on 2 columns

  1. #1
    Forum Contributor
    Join Date
    02-09-2004
    Posts
    154

    need formula to count number of rows based on 2 columns

    Basically I have column P called scheduled completion, which contains either "closed", "Completed", "Complete", or a date. And column Q which is "date completed".

    What I need to do is count the number of closed/complete/completed rows, where the date in Q is during the last 7 days. i.e. I need to count the items which have been closed during the last week.

    I have achieved this by a dummy column R which has =IF(AND((Q297>(NOW()-7)),(OR(P297="Complete",P297="Completed",P297="Closed"))),1,0) to put a 1 if its closed etc and the date in P is within the last week. I can then count the number of 1's for a total of completed items in the last week.

    The major problem I have is that when people add new items to the bottom of this list, the formula is not going to automatically go into the new row, even though "extend list formats and formulas" is on, it just never does it here on our excel 2002 so this is not an option. Therefore what I MUST have is a formula which does not need a dummy row to count this, but which will work regardless of the length of this list (from P13 to wherever the bottom of the list is). Otherwise they will add new rows and the formula result is going to end up outdated and wrong.

    Anyone?

    Thanks
    Last edited by neowok; 01-05-2005 at 09:33 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1