When I was Bahai, I believed in the power of prayer. When I was a Charismatic Evangelical, I believed in the power of prayer. When I was LDS, I believed in the power of prayer. When I was a Unitarian, I was uncertain about the efficacy of prayer. As an unaligned Skeptic, I'm pretty sure it doesn't do any good, but neither does it do any harm, unless it is done in replacement of taking other action.
To those who believe in the power of prayer, being in someone's prayers is a very positive thing. To those who don't, it at least shows that the person who is praying for you is concerned, so long as it is done in addition to other possible actions of assistance, rather than instead of; if the person offering prayers is in no position to provide any other assistance, well, it does show they care.
Edit to add:
Given that, I will admit that if someone told me I was in their prayers, my internal reaction, which I would strive to keep from them so as not to offend them, would be, "What a stupid waste of your time and energy!" But, recognizing what it means within their belief system, I would never say that to their face, or in a context where they would feel it was directed at them specifically. Why cause them pain needlessly?