# Thread: Counting on date and code (2003)

1. ## 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. ## 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,))

Macropod,

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

6. ## 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. ## 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,))

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

9. ## 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. ## 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
•