Most Helpful Guy
Normally its referring to a guy friend that has genuine feelings for his female friend. While she is out getting her heart broke she confides in him to cheer her up but never seeing him as a guy that would be anything more than a friend.
Even though he may legitimately love her, it never happens.
That's the basic explanation.
Chances are the ones you friendzoned are the ones that want to be a reason you smile day to day or want you to have the support you need to achieve your dreams and not in it just for booty.