Sorry for the delayed reply, I have been out and about. I'm certainly no expert either, but know a little law and have my own experience signing one of these.
Re someone like Weinstein, an allegation of a criminal act is not the same as proof of one or a verdict in a criminal court that one has occurred. You have someone with an accusation who has to make a big decision to pursue a criminal case (and potentially lose). And win or lose, they have a good chance of destroying their career, because who wants to employ a potential troublemaker.
So the consideration for an NDA with a person alleging abuse is likely to be something along the lines of enhanced monetary compensation for termination of employment - enhanced in exchange for a gagging order on discussing or pursuing anything that happened during the term of that employment; or something similar that falls well short of any admission of criminal liability or other wrongdoing. Enough money to match, eg, civil damages for an equivalent injury and enough to bribe them to shut them up and go away, essentially, knowing that if they open up that can of worms they may lose what they have gained (bargained for).
Is it enforceable? Well you don't know until you test it by breaking it. But who is going to risk breaking it? Unless they know for certain they can have the Weinstein-esque person banged up, are they going to risk putting themselves through a criminal or civil court case and potentially losing? Killing off their career in the process? Having to pay back the compensation they received under the NDA if they lose? Just because Weinstein is a proven criminal, doesn't mean he doesn't still have friends in the industry who can kill off your career - plenty of powerful people never condemned him (cough, Oprah, cough). Taking on a big time Hollywood producer is probably as scary as taking on the Mafia. You won't get killed with a machine gun, but you may well end up as good as dead. It takes many people coming forward to open the floodgates and face the consequences to take on a man like him.
I think you mentioned Invictus volunteers signing NDAs. I don't know what the consideration would be, but possibly it's "payment" in the form of tickets, family benefits, merchandise, that type of thing.
If Sophie had resigned as requested from an unpaid, voluntary job, they couldn't force her to sign an NDA unless they were offering her compensation, ie offering her a bargain she wasn't already entitled to automatically. If she'd accepted a pay off and signed an NDA, then breaking it would be down to her arguing that it was unenforceable either because its terms were too broad, that they wanted to cover up an illegality committed against her or others of that she qualifies as a whistleblower.
An NDA may be partially valid and partially invalid too. So it might be enforceable with regard to keeping trade secrets, but not with regard to keeping the secret that the company forged share certificates or something else illegal.
I think another pp said they are a very over-used tool, and whilst I am no expert, I certainly get the sense that this is the case, and that they are proffered left, right and centre to bamboozle the less powerful party into thinking they have to sign them in order to form or end a working relationship.