How to connect different criteria among 3 sheets?

unclear
what do you expect to see and why?

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'?

Attach a file here as many respondent do want to visit file-hosting sites...

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

In D2

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

in I2 of "Sales" (was in column J)

=SUMPRODUCT((Compensation!$D$2:$D$169)*(Compensation!$A$2:$A$169=$A2)*(Compensation!$B$2:$B$169>=Summary!$K$1)*(Compensation!$B$2:$B$169<=EOMONTH(Summary!$K$1,0)))

this will correct the formula.

Second, the "helper" column(s) can be in a separate sheet: I cannot find a way to do this without "helper" column(s) BUT I'll leave it to the other experts to look at..

However, with 15000 rows, then the [potential] use of array formulae (as a solution) will adversely impact on performance so do not dismiss using "helper" columns.

Thank you!
Yes, this does give correct amounts.
The issue with 'helper' columns remains. I can't figure this out, too.

Thank you for your input!
I hope someone might still have a bright idea how to solve my problem.

The issue with 'helper' columns remains. I can't figure this out, too.

..... meaning ???

Originally Posted by JohnTopley

..... meaning ???

Meaning, neither you nor I can't figure out a way to avoid them. :-)

... OK

... but why the reluctance to use them: they can be the most effective and efficient solution to problems like yours.

Originally Posted by JohnTopley

... OK

... but why the reluctance to use them: they can be the most effective and efficient solution to problems like yours.

Because I work with, well, idiots.
If I change 1 thing in their usual template it will cause mass mayhem and confusion.

Put the "Helpers" in hidden sheet ???

Originally Posted by JohnTopley

Put the "Helpers" in hidden sheet ???

I guess I'll try that. Let's hope it works, and no one looses their minds. :-)
Thank you!