I posted this back 2/7/09 and figured out how to make it work but don't know
why and was wondering why it does?

=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D3),--(WeekInvoice=$E$1),INVNET)
I changed to:
=SUMPRODUCT(--(CustomerNumberInvoice=$D3),--(WeekInvoice=$E$1),INVNET)

The worksheet is named Customers and contains the formulas with the customer
number in column D that it matches on the other sheets/dynamic ranges. When
I sort who purchased the most, the first formula puts the customer names in
column A in order but the formula gives the wrong dollar amounts. It returns
the amount of the customer that is now is the position that it used to be.
If customer #30 had the most and was in row 31, it sorts and puts #30 in the
second row (first 2 rows have headers) but pull info from what ever customer
is now in row 31. The formula has
=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D31),--(WeekInvoice=$E$1),INVNET)
in the second row instead of
=SUMPRODUCT(--(CustomerNumberInvoice=Customers!$D3),--(WeekInvoice=$E$1),INVNET)
When I deleted the "Customers" from the formula, all sorts properly.
Just wondering why it has to be this way?
Thanks,
Lee