You know, I was thinking of a game with a good weird scream. Master of Magic. Whenever you banish a wizard, they let out a good weird scream when you banish them.
https://youtu.be/qeT2QEtfSV0?t=2728
(45:28)

Exile II: The Crystal Souls has a great scream when your party is defeated. It expresses the frustrations of the player quite well as well.

Soldier of Fortune has a few corckers like

Impossible Mission (C-64):
https://www.youtube.com/watch?v=m0U6h5mvr9E

Portal:
https://youtu.be/-LM8hG_g8dk?t=209

Since we're on GOG:
The Witcher 3:
https://youtu.be/mhbmWgd2f2c?t=1930

And I mean - this is basically the definition of weird:
Half-Life:
https://www.youtube.com/watch?v=fAmrnnKDsCg

Screams are a major reason to play Guns Gore and Cannoli 1 & 2.

(Epic one-liners and constant awesomeness are secondary in front of that.)

Good scream? Say no more:
https://youtu.be/bl919yZj6GM

On a more serious note, Doomguy's 2nd death scream (when a shot kills you and also brings your health to high negative values) is a classic:
https://youtu.be/4xQ8Rto2GB8

Then there are of course the falling sounds for Duke and Lo Wang:
https://www.youtube.com/watch?v=O-ev0XASzok
Can't find the one for Lo Wang sadly.

I also like the death screams of the Hata Mari enemy type in Shadow Warrior 2. Can't find a video unfortunately

And let's not forget the cultist death sounds from Blood.
https://www.youtube.com/watch?v=Fb0dO894x5s#t=0m21s

Disciples 2 has some nice screams when a hero gets defeated, such as: https://youtu.be/vQDtFh0hjb0?t=110 Or the D.O.L.L.s of Zilla City :P.

Yeah, pretty much the entire game. Love sound design in SW 2. The human enemies scream nicely too, the robotic soldiers, just perfect.

So that's what it sound like to be on the other end of the BFG.

Crudux cruo!
...

Ah!

Silent Hill 3, particularly the mannequin room shortly before reaching home.

BFG can trigger it, depending on the damage roll of the projectile. But it will most likely gib you more often than not and that's a death without a scream. You get gibbed when the final hit brings your HP to -100 or more.
For the second scream to play, the killing shot has to land you in the -51 to -99 HP range. That means you can only get it by getting hit by a rocket launcher or a hard hitting enemy rolling a max damage roll on you while very low on health. At 0 to -50, the standard death sound plays.

Impossible Mission is the game that comes to mind first.

In the age of 8 bit computers sampled voice in games was practically unheard of which made the scream even more haunting. It left an impression that is not even forgotten now, almost forty years later.

Bad screams can kill all the fun.

There are civs in Star Wars: Galactic Battlegrounds I no longer want to play with/against due to how annoying are the death screams of some of their units.