SELECT COUNT(*) FROM offers AS o
   LEFT JOIN offerhistory AS oh ON o.id = oh.offerid
   JOIN offer_ip_log AS oil ON oil.offer_id = oh.offerid AND oil.member_id = oh.member
   WHERE active=TRUE AND pay_type='0'
   AND cc_required LIKE '%'
   AND us=TRUE 
   AND status IN (0, 1) 
   AND member_id = '229400' OR (ip = '24.117.33.169' AND expired = false)
   AND oh.offerid IS NULL

I am not sure about status, member_id, ip and expired tables, so I left out the qualification with table name. You might need it if you get some Column 'blah' is ambigious errors.