Formula Help

[Viking88]

How can I change this formula so I do not have to use an array function. Right now the array calculations are eating up the calucaltion time and making the file much larger than it needs to be:

=SUM(IF((Sheet1!$T$6:$T$80000=$B29)*(Sheet1!$J$6:$J$80000=$A29),Sheet1!$Z$6:$Z$80000,""))

Thanks.

 

[njimack]

=sumproduct(--(Sheet1!$T$6:$T$80000=$B29),--(Sheet1!$J$6:$J$80000=$A29),(Sheet1!$Z$6:$Z$80000))

Do you actually need to reference 80,000 rows of data? You might be better off using dynamic ranges.

 

[Viking88]

No I don't need to reference that many rows, but I want to make sure everything is picked up.

What are dynamic ranges and how can I incorporate them?

 

[njimack]

No I don't need to reference that many rows, but I want to make sure everything is picked up.

What are dynamic ranges and how can I incorporate them?

http://contextures.com/xlNames01.html#Dynamic