In short my question is this. If I use the SUM function (or some other function) that can take one or more arguments, is there some way I can pass in these arguments as an array which varies over some variable? For example,
SUM(CONCATENATE("1", A1)) and then have it go through a range in column A.
Longer explanation, my situation is this. I have a number of sheets in which there's a column for which I want the sum, based on the value of another column.
I have another sheet with the sums for values A and B. Using the
INDIRECT function let me do this for Sheet 1:
The formula used is
As you can see, it yields the correct sum for values A and B for Sheet 1. What I'd like to do now is add values to column D as more sheets are added in the future, and have the sum calculated over all the sheets. Can I do this using formulas, or is there need for a scripting function? I've tried using
ARRAYFORMULA but the end result is that the names of all the sheets get concatenated for the
INDIRECT function, so that doesn't work.
I feel that there must be some way to define a variable in a formula and then have it evaluated for an array of values for that variable, but I can't quite figure it out.