Summing Every 40th cell?

[Guest]

How can I Sum

A43+A83+A123+A163 into infinity.

This is crucial to making a worksheet to track #'s of resumes from various
web postings over time.

 

[Harlan Grove]

How can I Sum

A43+A83+A123+A163 into infinity.

....

Two ways.

=SUMPRODUCT(--(MOD(ROW(A43:A65536),40)=3,A43:A65536)

=SUMPRODUCT(N(OFFSET(A43,40*(ROW(1:1638)-1),0)))

The latter does less work.

 

[Myrna Larson]

=SUMPRODUCT(--(MOD(ROW(A43:A65536),40)=3),A43:A65536)

If it doesn't work as I show, try entering it as an array formula with
CTRL+SHIFT+ENTER.

 

[Jason Morin]

But the latter contains a volatile function. Naughty.

 

[Aladin Akyurek]

...

Two ways.

=SUMPRODUCT(--(MOD(ROW(A43:A65536),40)=3,A43:A65536)

=SUMPRODUCT(N(OFFSET(A43,40*(ROW(1:1638)-1),0)))

The latter does less work.

The temporal profile of the latter, across runs, seems to converge to:

Re Calc: 4.0 millisecs
Full Calc: < 10 millisecs
% Volatility: About between 50.0 and 100.0
Microsecs/Formula: About between 4,000.0 and 7,100.0

=SUMPRODUCT(--(MOD(ROW($A$43:INDEX($A$43:$A$65536,B1))-ROW($A$43)+0,40)=0),$A$43:INDEX($A$43:$A$65536,B1))

with B1:

=MATCH(9.99999999999999E+307,A43:A65536)

we have:

Re Calc: 0.4 millisecs
Full Calc: Around 50 millisecs
% Volatility: Less than 1
Microsecs/Formula: Around 27,500.0

Substituting CELL("Row",$A$43) for ROW($A$43) worsens the performance
considerably.

I wished FastExcel could be asked to produce average values across N
runs, where N can be set by the user.