Producing a SUM Summary Table

Hi, I have a demand matrix with 33 origin zones and 33 destination zones. The data in the table reflects the number of trips from an origin (rows) and a destination (columns).
My 33 zone system is too complex, so I'll be simplifying it into a 24-zone system. This means some of the old zones will be grouped together to form new, bigger zones.
I have created a reference table which specifies which of the old 33-zones belong to each of the new 24 zones.
I want to create a new demand matrix for the new 24-zone system which summarises the movements between (new) zones.
If a new zone is not composed of old zones, its cells in the demand matrix should be blank.

Can anyone suggest a formula that will work for this?

I've provided an example Excel file and a practical example below.

The new 'Zone 10' is made up of the old '35205', '91004' and '91005' zones. The new 'Zone 8' is made up of the old '33311' and '91016' zones.
Cell (J49) in the new matrix would tell me how many movements there are from Zone 10 to Zone 8, so this would have to be the sum of all those origin -> destination combinations:
35205->33311
35205->91016
91004->33311
91004->91016
91005->33311
91005->91016

Any help would be hugely appreciated. Thanks!

Yes I'm working with Microsoft 365

Try in C40, copied down and across:

=LET(zones,BYROW($B$4:$B$35,LAMBDA(row,MAX(IF(row=$AD$40:$AF$63,$AC$40:$AC$63,0)))),SUM(IF((zones=$B40)*(TRANSPOSE(zones)=C$39),$C$4:$AH$35,0)))

Are you sure you have 365? Does it work in the attached?

Not sure that's correct Ali as I didn't think I was on the beta channel - I don't have things like VSTACK and TEXTSPLIT that you do but I do have LAMBDA and BYROW.

Though I don't know if you can get a version of 365 and not keep it updated? As was only a few months ago that I got them I think.

My version of Excel doesn't recognise BYROW (nor VSTACK or TEXTSPLIT)

I'd be interested to know when it was last updated...but anyway here is an alternative version without using BYROW:

=SUM(IFERROR(INDEX($C$4:$AH$35,MATCH(INDEX($AD$40:$AF$63,$B40,0),$B$4:$B$35,0),TRANSPOSE(MATCH(INDEX($AD$40:$AF$63,C$39,0),$C$3:$AH$3,0))),0))

Excellent, this works a charm. Thanks very much for all your help Nick, I'd not thought to use INDEX within the MATCHes.
FYI my version is Version 2108 last updated May 2022