SOLVED

New Contributor

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

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

=SUMPRODUCT((\$B\$20:\$P\$34)*(\$A\$20:\$A\$34=B59)*(\$B\$35:\$P\$35=\$O59))

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

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

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.

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

best response confirmed by AmaiaF (New Contributor)
Solution

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

Thank you. In AB21:

=SUMPRODUCT(\$B\$2:\$P\$16,ISNUMBER(MATCH(\$B\$17:\$P\$17,\$O21:\$AA21,0))*ISNUMBER(MATCH(\$A\$2:\$A\$16,\$B21:\$N21,0)))

Fill down.

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

Amazing, thank you so much @Hans Vogelaar!