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