"Harlan Grove" wrote in message
oups.com...
My fault. Didn't test. The col B formula can't be the same as the col C
and further right formulas.

Curious enough, if I put, in D2, either of the numbers 0 to 6 then C3
displays 27 to 33 respectively. This is very close to the goal but because
C2+D2=27 (30-3) then D3 should display 27 rather than C3, (i.e. To get
results in D3, D2 can = 7 to13, therefore D3 would = 27 to 33 respectively).

What I see happening, C3 is adding C2+D2 and if the sum is <20 or 27 then
results are "", but if the results are =20 and <=26 then I see reults.
Again however in C3 rather than the desired D3 (intended =27 and <=33)

I dare to point out that if by chance D2 was a 1 (rather than a 7) would the
formula then sum C2+D2+E2 to see if its sum is the intended =27 and <=33,
if not, then sum C2+D2+E2+F2 and so on (keep in mind the year) and
displaying the results in the final cell i.e F2.

In this case a revelation takes place (the following table changed for the
sake of argument and to correct an issue I didn't catch earlier). I
appologize, I got so wrapped up in this that I didn't make sure date (days)
were consistant with the count in row 2, and, B3 should have NEVER equalled
16.

A B C D
1 Date 8/29/02 11/6/03 11/8/03
2 Draft 16 20 2
3 New

A E F G
1 Date 11/13/03 11/18/03 11/25/05
2 Draft 5 5 7
3 New 26 31


ORIGINAL TABLE:
A B C D
1 Date 8/29/02 11/6/03 11/8/03
2 Draft 16 20 7
3 New 16 27

A E F G
1 Date 12/5/03 12/9/03 11/6/05
2 Draft 5 2 7
3 New 32




B3:
=IF(ABS(SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2)-30)<=3,
SUMPRODUCT(--(YEAR(B$1:$IV$1)=YEAR(B$1)),B$2:$IV$2),"")

C3:
=IF(AND(SUMPRODUCT(--(YEAR($B$1:B$1)=YEAR(C$1)))=0,
ABS(SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2)-30)<=3),
SUMPRODUCT(--(YEAR(C$1:$IV$1)=YEAR(C$1)),C$2:$IV$2),"")