SOLVED

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

 AmaiaF_2-1650927604628.png

I then have this dataset:

AmaiaF_0-1650928212927.png

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

@AmaiaF 

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)
Solution

@AmaiaF 

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.