How to loop in excel without VBA or macros

Question

Is it possible to iterate (loop) a group of rows in Excel without VBA or macros installed? A web search didn't yield anything helpful.

=IF('testsheet'!$C$1 <= 99,'testsheet'!$A$1,"") &
IF('testsheet'!$C$2 <= 99, 'testsheet'!$A$2,"") &
IF('testsheet'!$C$3 <= 99, 'testsheet'!$A$3,"") &
... and so on through !$C$40, !$A$40 ...

As it is, I'll have to repeat the above code 40 times in each cell and I have over 200 cells which need the code.

I'm pretty good with PHP/SQL, but just learning Excel.

Answer 1

I'd like to add up all of the odd row columns.

SUMIF has TWO potential ranges: one from which to sum and another from which to consider criteria.

SUMIF(B1:B1000,1,A1:A1000)

If a cell in the B range is "=1," this function will sum the associated A cell only if it is.

I placed this in B to get "=1" to return in the B range:

=MOD(ROWNUM(B1),2)

Then auto fill down to get the modulus to fill; you may also set calculatable criteria here to get the SUMIF or SUMIFS conditions you'll need to loop over each cell.

It's easier to use than ARRAY, because it hides the loops at the rear end!