Formula not based on range but when the records stop in column I

[mcranda]

=SUMIF(J4:J60000,"",P4:P60000)

Above is my formula. The range is to 60000 because the end of the records vary. Formula is in P1. I want it to be based on the last records in I.


There are similar formulas in M2 and P2 that I will use the same logic for to only sum at the end of the records in column I. All the data will have varying rows.

[GregTheSquarePeg]

Hi,

Sorry if I'm missing the point with this, but - why can't the formula just refer to a long range? If the number of rows will be changing (increasing), it's going to be difficult for a formula to account for that, without referencing a long range.

[Tony Valko]

SUMIF is a smart and efficient function.

It will only calculate up to the used range.

If you have data down to row 1000 then both of these SUMIF formulas are equally efficient:

=SUMIF(J4:J1000,"",P4:P1000)

=SUMIF(J:J,"",P:P)

[mcranda]

Will the formula with the long range make my spreadsheet bigger? I am running into problems because I have numerous formulas extended to longer ranges to include all the data that my spreadsheet is huge.

[Tony Valko]

It depends on what those formulas are.

See this for tips on improving efficiency:

http://www.decisionmodels.com/