PDA

View Full Version : Solved: Counting different values



SeanJ
07-10-2008, 11:11 AM
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
07-10-2008, 12:49 PM
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
07-11-2008, 03:41 AM
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
07-11-2008, 03:49 AM
.

SeanJ
07-11-2008, 06:25 AM
Thanks for the help.