Results 1 to 7 of 7

Help with - =if,and,or combined with countif

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    879

    Help with - =if,and,or combined with countif

    Good afternoon happy campers,

    Worksheet =
    Column A (range - A5:A70) - name
    Column C (range C5:C70) - Date required
    Column E (range E5:E70) - Date completed
    Cell K2 - 01/01/2012
    Cell L2 - 31/12/2012

    In the above, lets say a team of people are scheduled to complete a task every week, the sheet contains dates from last year through to planned dates for next year.

    Any weeks that pass without a check, a question mark (?) is placed into Column E relating to the expected date in Column C (to simply highlight "why is it not done")

    Now i want my formula to calculate:
    Out of all the info in Column C, only utilise 2012 (between K2 & L2 'start of year & end of year')
    Calculate how many times the question mark (?) appears
    AND
    Out of this data, show me how many of the cells (in this year) contain a date.
    I have added the formula
    =IF(E4="","",IF(E4-C4=0,"On-Time",IF(E4="","",IF(ISTEXT(E4),"","OverDue"))))
    to show me if the check was completed on/after its dues date - works fine.
    After seeing this work i became a little 'Excel' happy, and jumped into 'IF,OR,AND' to try and get the result as mentioned above.
    =IF(OR(AND(C5:C70>K2,C5:C70<L2),AND(E5:E70,"?")),COUNTIF(E5:E70,"?"))
    But i only receive a #VALUE error, and am stumped on what im doing wrong.
    I think it may have something to do with my COUNTIF part but im unsure, could someone please turn the light on for me lol

    kind regards, galvinpaddy
    Last edited by galvinpaddy; 06-23-2012 at 10:05 AM.

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