How to connect different criteria among 3 sheets?

I used a "helper" column : column I in "Sales" but this can be any column you choose and can be hidden

in I2 in "Sales"

=SUMPRODUCT((Compensation!$A$2:$E$169=$A2)*(MONTH($B$2:$B$169)=11)*(Compensation!$D$2:$D$169))

copy down

The highlighted value (11 for November) is the month and would be better if you placed it in a cell: it appears compensation can vary with date.


In "Summary"

in D2

=SUMPRODUCT((Sales!$D$2:$D$15=Summary!A2)*(Sales!$I$2:$I$15))

OR

=SUMIFS(Sales!$I:$I,Sales!$D:$D,Summary!A2)

Copy down

I note you multiple Product ID entries with the same date/same value: very confusing !?

Thank you for the reply. Unfortunately, this didn't help.

1) I can't add any 'helper' columns in 'sales'. I'm trying to find a way around it, that's the whole point. If I could, this wouldn't be a problem I can't solve.

2) SUMPRODUCT formula you tried in 'sales' does not work correctly. It does not give the sum of all compensations received during current month for each item sold. See for yourself, I added corrected sums.

As it is, many items can receive several compensations on the same date and in the same amount. That's how it works. I can receive 500 amounts in one day for 37 items sold, and those items do not necessarily have to be sold in the same month. There could be 47 amounts scattered there for 5 items sold in, say, August.
In reality I'm working with huge amount of data where sheet 'compensations' contains data for 18 months with some 15000 rows.

The question is, is there any way I can get compensation amount DIRECTLY from 'compensations' to 'summary'?