Date count

[Syahira]

Hello everybody
Let say I have these data:

Centre Name Date Open
Santa Maria August 2004
St. Loius April 2006
Greenlane March 2002
North Point December 2005
South Point May 2006

This is a part of the data that I have.

My question are:
1) How to count how many centre open before 2006?
2) To list the centre open before/after 2006
3) To list of centre that have operated 1 year or more?

Thanks (^_^)

 

[Bob Phillips]

1. =SUMPRODUCT(--(YEAR(B2:B100)<2006))


2. Select D120, enter this formula in the formula bar

=IF(ISERROR(SMALL(IF(YEAR($B$2:$B$20)<2006,ROW($A2:$A20),""),ROW($A1:$A20)))
,"",
INDEX($A$1:$A$20,SMALL(IF(YEAR($B2:$B20)<2006,ROW($A2:$A20),""),ROW($A1:$A20
))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

3. Similarly, in F1:F20

=IF(ISERROR(SMALL(IF(($B$2:$B$20<>"")*(TODAY()-$B$2:$B$20>=365),ROW($A2:$A20
),""),ROW($A1:$A20))),"",
INDEX($A$1:$A$20,SMALL(IF(($B$2:$B$20<>"")*(TODAY()-$B$2:$B$20>=365),ROW($A2
:$A20),""),ROW($A1:$A20))))

again array formula.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)