Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Counting on date and code (2003)

    Loungers,

    I need to be able count the number of occasions that the codes (LTI, MTI etc) below occur in the 12 months prior to the date in column A (in this case Mar-06)

    Mar-06| LTI MTI FAC AD Hazard Enviro

    Any suggestions

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Counting on date and code (2003)

    Hi Dean,

    To test for a single code, you could use and array formula like:
    =SUM(IF(NOT(ISERROR(FIND("MTI",B1:B20)))*(A1:A20>D ATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),1,))

    To test for the presence of any one or more of the codes, you could use and array formula like:
    =SUM(IF((NOT(ISERROR(FIND("MTI",B1:B20)))+NOT(ISER ROR(FIND("LTI",B1:B20)))+NOT(ISERROR(FIND("FAC",B1 :B20))))*(A1:A20>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),1,))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Counting on date and code (2003)

    Macropod,

    Thanks for your assistance - will give your suggestions a go

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Counting on date and code (2003)

    macropod

    I've tried your suggestions below, however I neglected to say in my previous post that the dates recede in a different tab called "Incident Sheet" in K3:k500 and the code that I want to count the occurrences of are in the date range that resides in "Incident Sheet" I3:I500.

    I've tried to modify your formula below, but it is not returning the correct number in the count


    Any other suggestions?

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Counting on date and code (2003)

    Hi Dean,

    There's an easy way of getting the code right - simply input in on the 'Incident Sheet' and, when you've got it working there, cut & paste it to whatever output sheet you're using. Using absolute range addressing, the first of the above formulae would come out looking like:
    =SUM(IF(NOT(ISERROR(FIND("MTI",'Incident Sheet'!$I$3:$I$500)))*('Incident Sheet'!$K$3:$K$500>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))),1,))
    You could, of course, code it like that on the output sheet without using cut & paste.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Counting on date and code (2003)

    Thanks Macropod

    I had modified your formula as you suggest - I think that the problem maybe that I can't see where the formula refers to the selected month (ie that resided A1 in the original post) - I'd like to be able to change the month/Year and the occurrences of the codes within the selected 12 month period are counted.

    I hope all that make sense - again thanks for your help

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Counting on date and code (2003)

    <P ID="edit" class=small>(Edited by macropod on 08-Feb-07 17:59. Revised coding to check 12-month period before date entered in A1, instead of all dates since one year before)</P>Hi Dean,

    I think I misunderstood your requirement. try this version of the formula, which tests the dates in 'Incident Sheet'!$K$3:$K$500 against the date in A1 on your output sheet - previously, I was testing against today's date:
    =SUM(IF(NOT(ISERROR(FIND("MTI",'Incident Sheet'!$I$3:$I$500)))*('Incident Sheet'!$K$3:$K$500>DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)))*('Incident Sheet'!$K$3:$K$500<DATE(YEAR(A1),MONTH(A1),DAY(A1) +1)),1,))
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Counting on date and code (2003)

    Macropod,

    Thanks again - this is very close, but for some reason the count of the matches with the date range (ie past 12 months from the month selected - sheet 2 A9) are incorrect.

    I've attached a cutdown version of the spreadsheet that may help - in Sheet 2 the occurrence of "LTI" = 3 in the 12 months to the end of Dec 06, however if I filter 1n Sheet 1 there is 2
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Counting on date and code (2003)

    Your date is December 1, 2006. The 12 months prior to that are December 1, 2005 through November 30, 2006, not through the end of December 2006.
    Please explain exactly and unambiguously which date range you want.

    Macropod's formulas count cells in which the text LTI occurs - this includes cells containing "Non Stat LTI". To count only cells whose value is "LTI", change

    NOT(ISERROR(FIND(B9,Sheet1!$I$2:$I$403)))

    to

    (B9=Sheet1!$I$2:$I$403)

  10. #10
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Counting on date and code (2003)

    Thanks Hans

    Sorry about the date details - get a bit confusing.

    Your suggestion seem fine <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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