try this

=sumproduct(('Clearence Status sheet'!G1:G1000="Offer Withdrawn")*('Clearence Status sheet'!L1:L1000>=38108)*('Clearence Status sheet'!L1:L1000<38138))

note that a row range needs to be specified - i used rows 1:1000