I can only understand that they are great for older and/or overweight people who have trouble getting in and out of lower cars. They don't need to be that large though

Stelvio is a pretty good sports car SUV thing.

What would you recommend then in the countryside for a family of 5 or more with ample snow in the winter and lots of gravel roads? That can accommodate taking the kids to soccer and to/from school no matter the road conditions? As well as be used to go ski and other car based vacations like going to a beach resort/camping for a week. All of the above being very normal requirements for a family car in at least the Nordics.

A normal station wagon type generally has too low ground clearance but there are exceptions like the cross country models of Volvos. They also only have 5 seats so they just barely fit the family with no additional friends tagging along, and very few can handle double child seats without dropping down to max 4 people. For older kids then yeah it works well, if you don't need the ground clearance, and you can pack on the roof if the trunk isn't big enough.

A large pickup truck has issues with being usable for the vacation stuff. Sure you can pack on the flatbed and there are "houses" to give it cover but it's hardly smooth, and they're even larger and more expensive than a SUV.