Resident Evil 2 Remake, Resident Evil 3 Remake, and Resident Evil 5 have the best stories in the franchise. Yeah, I said it. With the Remake stories in particularly, I find legit good, because they keep it simple. Yes, their remakes of their original counterparts but, let's be honest, the originals had even more problems with their stories.
RE5 story is simple, but it's remained focused. I also don't mind the more serious direction, because it's actually done well. The problem with Resident Evil 4, is that stuff just happens. Sure the villains are more entertaining, but there's like a whole bunch of them with little to no personality other than "I'm evil hahaha! I send out insults! You American worm, die!". Plus, the cult was a footnote in the Resident Evil history, because they're nothing more than bit players to Wesker. Plus, Ada is just annoying and not interesting, and became less sympathetic (we are supposed to like her or see her as a badass anti-hero). This is the start of her getting worse. While I like Leon, it sucks they would not get his original voice actor back, and they pretty much turned him into human Dante. Ironic, because Dante originally looked like a white haired Leon.
It doesn't help that RE4 went through so many versions, and Capcom has spent a lot of money on it at the time. Shinji just wanted to make the game work, so he said screw the story just throw stuff in there and make it work. Either the game was going to sell gangbusters or bomb, there was no in between. Everyone got their wish for the time, but all that starts to show when you stop and look at it for more than a minute.