Sum all cells in table whose row and column headers match arrays

New Contributor

Hello! Got an issue and would love ideas.

I have an origin/destination matrix with 15 zones as follows


I then have this dataset:


I need the results I have column AB here, but I'm looking for a universal formula that I could simply drag down. With a sumproduct formula I can only match one row&column pair at a time, for example


This gives me the pair 2>15, but I'd like it to give me the sum of pairs 2>15, 3>15, 4>15, 5>15.

Hopefully one formula can do the trick, it might need a script but I'm not too savvy on them so can't come up with one.

I hope it's clear enough, thanks in advance to whoever is nice enough to give this a minute!

4 Replies


Could you attach a sample workbook, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

best response confirmed by AmaiaF (New Contributor)


Thank you. In AB21:


Fill down.