Feed Multi-Sheet Array into SUMPRODUCT formula for Conditional Sum calculation

[rkaczano]

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

 

[Peter_SSs]

One last question,

The following would allow us to know what resources you have available.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[rkaczano]

I can confirm that both options suggested work (Volatile using INDIRECT and non-volatile using XMLFilter/Text Join. I like both solutions but I may end-up using the non-volatile option.

Currently I get a MIN using

= MIN(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,'<<Sheet1:Sheet8>>'!D49)&"</y></x>","//y")*TrueFalseArray)
= MIN({44713;44835;44835;45231;45231;45231;45231;45231}, {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE})
= 0

The TRUE/FALSE array creates zero values that I want in some instances, but when I am calculating a MIN the MIN returns. But I want the MIN of the results that are TRUE (i.e. the third value 44835).

The following below does not work

=MINIFS(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,'<<Sheet1:Sheet8>>'!D49)&"</y></x>","//y"), TrueFalseArray, TRUE)

When I evaluate the formula using F9 within the formula bar I get the following below.

=MINIFS({44713;44835;44835;45231;45231;45231;45231;45231}, {FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}, TRUE)

Not sure why this does not work.

The only way I can get this to work is by using a MIN formula with an embedded IF stmt within an ARRAY formula (CNTL/SHIFT/ENTER)

{=MIN(IF(TrueFalseArray=TRUE, FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,'<<Sheet1:Sheet8>>'!D49)&"</y></x>","//y")))}

Why is this?

 

[Peter_SSs]

It is a pity you seem unprepared to update your details or identify what excel version(s) you have access to. As I have pointed out twice, different versions may provide different solutions.
You have indicated that you have TEXTJOIN which narrows down your versions to Excel 2019 or Microsoft 365 however, those two versions have different other features that could impact these calculations.
I was waiting to identify the version but instead will offer choices.

I am evaluation Sheet1:Sheet4 with the following values in A1 of those 4 sheets
2, 4, 6, 8

rkaczano.xlsm
	A	B	C	D	E
1		365	365 & 2019
2	SUM	16	16		1
3	MAX	8	8		0
4	MIN	2	2		1
5	AVERAGE	5.333333333	5.333333333		1
Summary

Cell Formulas
Range	Formula
B2	B2	=SUM(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
C2	C2	=SUM(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B3	B3	=MAX(FILTER(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y"),myArray=1))
C3	C3	=AGGREGATE(14,6,FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*1/myArray,1)
B4	B4	=MIN(FILTER(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y"),myArray=1))
C4	C4	=AGGREGATE(15,6,FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*1/myArray,1)
B5	B5	=AVERAGE(FILTER(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y"),myArray=1))
C5	C5	=AVERAGE(IF(ISNUMBER(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")/myArray),FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")))

Named Ranges
Name	Refers To	Cells
myArray	=Summary!$E$2:$E$5	B2:C5