Quote Originally Posted by hrlngrv View Post
If you define the name Input_ALL referring to =Input!$1:$1048576, and the name ColumnLetters referring to =SUBSTITUTE(ADDRESS(1,COLUMN(Input_ALL),4),1,""), and you had AE in cell X97, 1 in X98 and 10000 in X99 in another worksheet, then in that same other worksheet,

Formula: copy to clipboard 
=SUMPRODUCT(--(MOD(ROW(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0)),15)=0),INDEX(INDEX(Input_ALL,X98,0):INDEX(Input_ALL,X99,0),0,MATCH(X97,ColumnLetters,0)))

would give you the same result as the array formula you show.

I'll be more direct: in the long run you want to avoid INDIRECT and OFFSET when possible. This is a situation in which it's relatively easy to avoid using them.

Simpler than the formula above would be

Formula: copy to clipboard 
=SUMPRODUCT(--(MOD(ROW(INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99)),15)=0),INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99))

Note: when X98 is 1 and X99 is 10000, INDEX(Input!$AE:$AE,X98) is INDEX(Input!$AE:$AE,1) is Input!AE1, INDEX(Input!$AE:$AE,X99) is INDEX(Input!$AE:$AE,10000) is Input!AE10000, so INDEX(Input!$AE:$AE,X98):INDEX(Input!$AE:$AE,X99) is Input!AE1:AE10000.
Hey thanks! That smiplified formula (using SUMPRODUCT as a non-array formula instead of the array formula I had been using) did serve the same purpose and with the added benefit of not having to be an array formula.

I haven't given the first portion of your suggestion a try yet, as it uses some features I'm not yet familiar with (for example defining names, as well as the SUBSTITUTE and ADDRESS functions), but I'll play around with those to get acquainted and give it a go.

There could be a minor snag in using Names, as the workbook also needs to be portable over to Google Sheets -- which I'm sure isn't within the scope of these forums, since they're for Excel users and not Google Sheets -- but I think you've really helped point me in a good direction.

Thanks so muche for the help everyone!