I know I can sum cell A1 across multiple sheets (numbered Sheet1 to Sheet10) using the following formula:
=SUM(Sheet1:Sheet10!A1)
Is it possible using this nomenclature to separate out the individual values in the range? For example if cell A1 in Sheet1 starts at “2” and increments by “2” in each sheet you would get the following 2,4,6,8,10,12,14,16,18,20
=SUM(Sheet1:Sheet10!A1)
= 110
But I want to be able to separate out the individual values in array form:
=NEWFUNCTION(Sheet1:Sheet10!A1)
=NEWFUNCTION({2,4,6,8,10,12,14,16,18,20})
I then want to use the returned array in a SUMPRODUCT formula with Boolean values like a conditional sum.
=SUMPRODUCT(NEWFUNCTION({2,4,6,8,10,12,14,16,18,20}), NEWFUNCTION({1,0,0,1,0,1,1,0,0,1})
Part of the issue here is that Sheet1:Sheet10!A1 does not evaluate into discreet values. I can wrap a TEXTJOIN(“,”,Sheet1:Sheet10!A1) around it to concatenate the values with a delimiter but the I am left with a text string that looks like this: “2,4,6,8,10,12,14,16,18,20” which cannot be used in the SUMPRODUCT formula. I can further attempt to split the text string into separate items using:
=FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(“,”,Sheet1:Sheet10!A1),",","</y><y>")&"</y></x>","//y"))
This creates an the array {“2”,”4”,”6”,”8”,”10”,”12”,”14”,”16”,”18”,”20”} but it wants to spill it across 10 rows. I was hoping I could figure out a way stop this from spilling and feed this “trapped” array back into the SUMPRODUCT formula. I also need to ensure that the string items are converted back to values prior to going into the SUMPRODUCT formula.
Thanks in advance
=SUM(Sheet1:Sheet10!A1)
Is it possible using this nomenclature to separate out the individual values in the range? For example if cell A1 in Sheet1 starts at “2” and increments by “2” in each sheet you would get the following 2,4,6,8,10,12,14,16,18,20
=SUM(Sheet1:Sheet10!A1)
= 110
But I want to be able to separate out the individual values in array form:
=NEWFUNCTION(Sheet1:Sheet10!A1)
=NEWFUNCTION({2,4,6,8,10,12,14,16,18,20})
I then want to use the returned array in a SUMPRODUCT formula with Boolean values like a conditional sum.
=SUMPRODUCT(NEWFUNCTION({2,4,6,8,10,12,14,16,18,20}), NEWFUNCTION({1,0,0,1,0,1,1,0,0,1})
Part of the issue here is that Sheet1:Sheet10!A1 does not evaluate into discreet values. I can wrap a TEXTJOIN(“,”,Sheet1:Sheet10!A1) around it to concatenate the values with a delimiter but the I am left with a text string that looks like this: “2,4,6,8,10,12,14,16,18,20” which cannot be used in the SUMPRODUCT formula. I can further attempt to split the text string into separate items using:
=FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(“,”,Sheet1:Sheet10!A1),",","</y><y>")&"</y></x>","//y"))
This creates an the array {“2”,”4”,”6”,”8”,”10”,”12”,”14”,”16”,”18”,”20”} but it wants to spill it across 10 rows. I was hoping I could figure out a way stop this from spilling and feed this “trapped” array back into the SUMPRODUCT formula. I also need to ensure that the string items are converted back to values prior to going into the SUMPRODUCT formula.
Thanks in advance