Solved: Counting different values

[SeanJ]

I hope I can explain this correctly. I was able to count number of test in a FY year. I need to now the number of systems per FY year. IF a system is test more then one in a year it is only count a one system for that year.

I started the code on the workbook attach.

[Bob Phillips]

I would just use formulae

For 2004,

=COUNTIF(Updated!B:B,">="&MIN(2000+ROW(A3))&"/10/01")
-COUNTIF(Updated!B:B,">="&MIN(2000+ROW(A3)+1)&"/10/01")

and

=SUM(--(FREQUENCY(IF((Updated!B2:B1000>=DATE(2000+ROW(A3),10,1))*(Updated!B2:B1000 <DATE(2000+ROW(A3)+1,10,1)),
MATCH(Updated!A2:A1000,Updated!A2:A1000,0)),ROW(INDIRECT("1:"&ROWS(Updated! A2:A1000))))>0))

the second is an array formula, just copy the 2 formulae down for 2005, etc.

[SeanJ]

The formula for FY04 return 2 but that that from the same system in that year. So the number of system should be 1.

For FY05 I am getting #value in the cell.

[Bob Phillips]

.

[SeanJ]

Thanks for the help.