I have found that, if I am compiling data using the SUMIF or SUMPRODUCT functions, and I sort my records, the function continues to reference the original cell reference. For example, if I use a referenced cell to fill the CRITERIA requirement, it will continue to use this same cell even after the list is sorted.
Sorting when using SUMIF & SUMPRODUCT
- Thread starter Spence
- Start date
Brianwarnock
Retired
- Local time
- Today, 14:39
- Joined
- Jun 2, 2003
- Messages
- 12,701
Why would you not expect it to?
Brian
Brian
Brian:
My expectation is that the reference would be relative. For example, if I sort a group of records that use the vlookup function, the reference to the source cell will change. With SUMIF and SUMPRODUCT it will act like an absolute reference. One way around it is to sort only the reference cells.
My expectation is that the reference would be relative. For example, if I sort a group of records that use the vlookup function, the reference to the source cell will change. With SUMIF and SUMPRODUCT it will act like an absolute reference. One way around it is to sort only the reference cells.
Brianwarnock
Retired
- Local time
- Today, 14:39
- Joined
- Jun 2, 2003
- Messages
- 12,701
I'm not sure what you are sorting all 3 functions basically behave the same.
1 You can sort the referenced array, with SUMIf and Sumproduct the result will not change, with Vlookup it may.
2 If you move the formula to another cell all of the references change in all formulae
3 if you sort the criteria cells then in all cases the criteria selected will change as the cell referenced in the formulae will not
Brian
1 You can sort the referenced array, with SUMIf and Sumproduct the result will not change, with Vlookup it may.
2 If you move the formula to another cell all of the references change in all formulae
3 if you sort the criteria cells then in all cases the criteria selected will change as the cell referenced in the formulae will not
Brian
Similar threads
