Replace INDIRECT in formula

So... what is wrong with:

=SUM(B:B)

or

=SUM($B$5:$B$10000)

ARRAY formula

=SUM($B$5:INDEX($B$5:$B$500000,MATCH(2,1/($B$5:$B$500000>0),1)))

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

If it's to do with efficiency, have you tried SUMIF?

=SUMIF(J:J,"<>")

https://exceloffthegrid.com/optimize...iple-criteria/

I hate to have to contradict you, but... you're wrong.

Here are the formulae, in order of speed:

1 SUM from 5-end. Time per calculation: 0.000101447
2. Whole column SUM. Time per calculation: 0.000103474
3. SUMIF Time per calculation: 0.000117064
4. Array formula. Time per calculation: 0.064025875 (hugely slow by comparison)


I suggest you read this article.

https://fastexcel.wordpress.com/2015...a-or-bad-idea/


However, you are ABSOLUTELY correct to want to move away from INDIRECT. On a sheet that size, it will be a disaster.

Thanks to all for your help! kvsrinivasamurthy I believe your suggestion meets my need at the moment. I just used your formula without entering it as an array, just as a normal formula without the {} and that seems to work for me. Glenn and AliGW, in the file I'm working with I had been using a number of SUMIF and SUMIFS with large ranges (e.g. an entire column) and it seemed to be dragging down my recalc time. If I narrowed the applicable range, I noticed an improvement in the processing time. Not an issue for me in smaller files. Appreciate your time! I'll circle back if my current path breaks down.

Were you addressing the OP or me, Glenn?

Certainly seems that my hunch about SUMIF will be of no benefit.

The OP... who seems to be going off in ENTIRELY the wrong direction here. The array formula will be hopeless.

I certainly agree with you on that!

I added in a few other options. IYou'll see that the results differ, this is because I added a value in way down near the end of each formula's range.

It is no surprise thta the array formula is hopeless.

I was surprised that SUMIF was also very slow, when it had to calculate to the end of the column. I was also a little surprised that the whole column SUM was slower by a factor of 2 than any of the other fixed-range SUMS.

Bottom line, anything up to 100K rows is zippy fast with SUM. SUMIF is about 10x slower and the array.... about 700 times slower.

Any improvement in processing time will be due to the removal of the INDIRECTs.

Have you heard of Dynamic Named Ranges? These are a smart alternative to fixed ranges. They auto-adjust to the length of your dataset, without you having to keep fiddling with them. I haven't done any systematic evaluation of their speed... but I think I'll spend the last 20 minutes of my session here, today, doing just that.

Do the majority of your formulae refer to columns that are blank, but which also have formulae in them (ie the blanks are formula blanks - null strings) or are they just blanks in the data ... ie the cells are EMPTY.

There is a big difference between an empty cell and a blank cell. More reading for you...

https://colinlegg.wordpress.com/2014...lank-or-empty/

So... in general, are the target cells for your formulae blank... or empty?

OK. I'm off in a few moments for the night. I'll write a note to myself to come back to you in the am wrt DNRs.

If, by the time you surface tomorrow, I have not replied (I've a mind like a sieve...) send me a PM to remind me.