Try this:

=SUMPRODUCT((ABS($C$2:$C$48)>2)*(ABS($C$2:$C$48)<4)*($G$2:$G$48="Y"))

Hope this helps.

Pete