Ok heres the formula I have so far:

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

This looks in the C column for numbers between 2 and 4 OR -2 and -4. Now, I need my formula to do this, AND each row that contains a value that is either between 2 and 4 OR -2 and -4, and for each row that contains a value in that range, I want to look in column G in that row and Count if that cell contains "Y"

Any ideas? Thanks guys