This formula in D39 should give the correct results

=SUMPRODUCT(($D$5:$D$10=LEFT(D$38))*($E$11:$R$11=RIGHT(D$38))*($E$5:$R$10=$C39)+($D$16:$D$21=LEFT(D$38))*($E$22:$R$22=RIGHT(D$38))*($E$16:$R$21=$C39)+($D$27:$D$32=LEFT(D$38))*($E$33:$R$33=RIGHT(D$38))*($E$27:$R$32=$C39))

copy across and down - see attached

This assumes that all 3 tables are the same shape - if not then you can use a similar formula with 3 separate SUMPRODUCTS....

[Edit: 16 was missing from Summary table - I added it in]

regards, barry

Example-barry.xlsx

=SUMPRODUCT(($D$5:$D$10=LE

copy across and down - see attached

This assumes that all 3 tables are the same shape - if not then you can use a similar formula with 3 separate SUMPRODUCTS....

[Edit: 16 was missing from Summary table - I added it in]

regards, barry

Example-barry.xlsx