Sorting problem when using SumProduct

Question

HI To All,

I have a spreadsheet with a Sumproduct formula   = SUMPRODUCT(('2010 Sales'!E$1:E$13897='2010 TA'!A2)*('2010 Sales'!D$1:D$13897="REG"),'2010 Sales'!C$1:C$13897)

When I sort the data it retains the absolute reference to the row number for the first criteria.  Trying to undersatnd why this happens

For example:

If John smith is in row 1 prior to the sort  and after the sort he is on row 10 the cell reference for the first criteria still references row 1 and not row 10.

Thanks again for any help

Thanks for any help

Answer 1

Hello,

Your Sumproduct usage looks quite similar to the one I present in the sample file at the bottom of this web page:

http://sulprobil.com/html/sumproduct.html

Maybe you find the other options better than your current approach.

Regards,

Bernd

---

http://www.sulprobil.com/html/excel_forums.html

Answer 2

Yes Bernad I'm using a pivot table now. I was trying to get a better understanding of what the system was doing with my orginal approach, so that I can keep it in mind in the future.

Thanks again