"Cheer up" means to lighten one's mood.
It can be said to someone else as a request, or perhaps more accurately as words of encouragement, for example:
Why are you looking so sad? Cheer up! It can't be that bad!
It can also be used to describe the action of trying to help someone else to lighten their mood, for example:
I'm going over to John's house to see if I can cheer him up.
You are right that it can be said in a way that shows a lack of sympathy for the other person's condition. Context and tone of voice are what make the difference. Obviously, if someone grumpily says "cheer up, for goodness sake!" they are probably not particularly sympathetic towards your situation.
With your specific example of bereavement, I have to say that "cheer up" does seem inappropriate for this context. Most people understand that grieving is necessary, and telling a bereaved person to "cheer up" would be thought of as inconsiderate. However, many people do appreciate some levity during such a time, and it does not seem inappropriate to say that you would like to cheer someone else up, if they were suffering a loss.