Whereabouts are you?
We are at a similar elevation and exposure as you are, in the south of Scotland. We have planted native trees and hedging all around our land, and right now we have some splendid colours. As well as colours, we have planted for wildlife, so trees and shrubs which have berries, or early pollen, or dense growth for nesting and roosting.
A mix of evergreens and deciduous trees works well.
We have included some Scots pine which gives green all year round and pine cones, also holly for the berries - that doesn't do well in boggy bits.
I agree about the cornus - there are various kinds with red, yellow or green stems, and flowers in spring/summer, berries in winter.
Field maple is lovely now, with beautiful luminous yellow leaves, both on the trees and on the ground (we don't remove fallen leaves). For gorgeous red autumn colours I think you would need to go non-native.......
One nobody has mentioned is Mirabelle (myrobalan or cherry plum) - you can buy bare rooted saplings from bulk nurseries for peanuts compared to those from fruit tree catalogues. Fruit is either red or yellow, and the birds will clear the upper unreachable branches for you. It's another golden yellow leafed tree in autumn, very light and clear.
Spindle, as mentioned, is very colourful, with weird artificial looking pink fruit which split to show bright orange seeds, and red leaves. It does grow very slowly and remains quite small, ideal for your lower storey. I didn't know about the toxic aspect, but our sheep haven't died, and the hens don't touch them.
Bird cherry and crab apples have red leaves, and again appeal to wildlife too.
Even some of the willows have good leaf colour, mainly yellow in autumn, with the advantage of very early pollen appearing at the ideal time for newly emerged queen bumble bees to feast on, and build up their strength after the winter.
I would include hawthorn, for various reasons, including flowers, fruit, perfume, nest sites, roosting sites and autumn colour - yellow. Hawthorn also doesn't lose its leaves all at the same time - right now we have some bare and some still fully green, so spreading its usefulness.
Even where the leaves are not splendid in autumn, berries can be, for example elder, rowan and wild rose hips.
Our hedges and coppices are now quite well grown and have changed the microclimate totally. Instead of arctic ice particles blowing in sideways and cutting us off at the ankles, we are protected to a great extent from all directions.
There don't seem to be many really good reds mentioned. One I am amazed at is blueberry. I know they're not native, but worth planting somewhere.
So my advice is to plant for colour and usefulness all year round, not just in the autumn. Wildlife loves pollen in the spring, nesting sites for birds, perfume (unbeatable is Balm of Gilead, quite a tall tree but the smell starts before the leaves appear in spring, and will spread downwind for a large distance), blossom, fruit, colour, shelter.
A variety of heights increases the density of the trees as a wind barrier and roosting/nesting site.
modified to add - I see you have doubled up this thread - it only needs to be in one place or it becomes muddled.