Forum Discussion
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!
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.
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.
Thanks for replying, here it is:
https://docs.google.com/spreadsheets/d/1h2ecnIslZd7jhteAVNbAPnyzUotu3c1R/edit?usp=sharing&ouid=109671450311875325475&rtpof=true&sd=trueThank 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.
