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

Thanks for responding. Yes your approach works better.

Do you know why sorting doesn't work in my Array formula?

I'm just trying to get a better understanding of how the computer reads the formula

 

Thanks again

 

 

 

Hello,

What do you mean with array formula, please? Your Sumproduct formula? Why dont you just use a Pivot table?

Just asking ...

Regards,

Bernd

---

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

Answer 3

Thanks for responding. Yes your approach works better.

Do you know why sorting doesn't work in my Array formula?

I'm just trying to get a better understanding of how the computer reads the formula

Thanks again

Answer 4

HI,

Thanks for responding: The offending cell reference from above is '2010 TA'!A2.

Thanks for any other suggestions

Ramone

Answer 5

Try rRemoving the $ from the offending cell reference. But leave it in front of 13897

Does that help?