There's clearly no "always applies" answer to that question, but on the whole I believe film scores should be noticed. They are not like sound effects or CGI where they are meant to blend seamlessly with what is on the screen so as to avoid revealing the mechanics of filmmaking. Noticeable film score does not distract from what is on the screen in this same manner and generally does not do so through an entire film (I dare someone to tell me where this piece shows up in A New Hope without looking at the track title). To provide the same rationale for avoiding it as you do other technical aspects of filmmaking is to ignore music as the emotional medium that it is. We have no inherent emotional relationship to sound effects or CGI, but music is entirely different. It is nearly always meant to make you feel something, and when it does that effectively it is triumphant. When it doesn't, it may as well not exist at all (which is what I think Tony's video was getting at).
However, I think it's an unfair standard to hold a composer or director's feet to the fire for not creating and perpetuating a "hummable theme". Noticeable does not necessarily = memorable in the sense that we think of people cheering to the opening title card of Lord of the Rings because we immediately recognize the string melody. I'll never hum Trent Reznor's "In Motion" from The Social Network or Johnny Greenwood's "Proven Lands" from There Will Be Blood, but these are some of the best film scoring in cinema and you damn well notice them when they are augmenting their respective scenes. What becomes memorable may not be the piece of music itself but the entire scene, which is usually because of an expertly placed piece of film scoring.
In fact, I think the downside to great film scores, and perhaps a subconscious deterrent for filmmakers, is that they can reveal the inherent weaknesses of a film. This is an exciting and powerful piece of music, and it feels slightly wasted on a shit Bay movie. In fact, first few times I heard it used in trailers (which it often is) I couldn't remember what film it was from. There have been, what...a dozen Marvel movies at this point? None meant to stand out above the others. You may be able to elevate a mediocre film to a higher level with some brilliant music (e.g., Conan the Barbarian) but if none of your films are meant to standout in the formulaic universe to which they were created, why would a composer even try?