Sumproduct Problem

[timbo1957]

Hi,

I have a download from a customer which has various account numbers relevant to them in column A sales in column G and a product ID in column H.

The problem with download is that is is 55,000 rows long because there is an entry for every sale.

So in column H the same product Id will appear several times.

I have tried this approach and it isn't working and I don't know why.

I ran a macro to copy the data to another sheet and delete any duplicates in column H to give me a clean list of unique product numbers.

Then I put this calculation in column G to try and bring in all of the sales.

=SUMPRODUCT((Download!$A$4:$A$49170=Customer!A4)*(Download!$H$4:$H$49170=Customer!H4)*(Download!$G$4:$G$49170))

I then copied it down the 13,000 odd rows on the worksheet.

But it's giving me the wrong figures.

Is it something to do with the custimer having more than one account number in column A?

[Pepe Le Mokko]

Perhaps a sample sheet would help?

Why not use a Pivot Table?

[Fotis1991]

Hi

If you use your formula only with Column H? Does this, gives you correct results?

=SUMPRODUCT((Download!$H$4:$H$49170=Customer!H4)*(Download!$G$4 :$G$49170))