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

 

[Dave Patton]

My example is not quite as large as your specifications; it yields results for the specified array.
The array could be a sheet range or hard coded or you name the information.

3D Sumif.xlsm
	H	I	J	K	L
1	Total and by Sheet Sumif			Sheets
2				1a	1
3	36			1b	0
4	36			1c	0
5				1d	1
6				1e	0
7				1f	1
8				1g	1
9				1i	0
Summary

Cell Formulas
Range	Formula
H3	H3	=SUMPRODUCT(N(INDIRECT("'"&$K$2:$K$9&"'!A1")),L2:L9)
H4	H4	=SUMPRODUCT(N(INDIRECT("'"&{"1a";"1b";"1c";"1d";"1e";"1f";"1g";"1i"}&"'!A1")),{1;0;0;1;0;1;1;0})

Named Ranges
Name	Refers To	Cells
Sheets	=Summary!$K$2:$K$6	H3

 

[Dave Patton]

3D Sumif.xlsm
	H	I	J	K	L
1	Total and by Sheet Sumif			Sheets	Include
2				1a	1
3	36			1b	0
4				1c	0
5				1d	1
6				1e	0
7				1f	1
8				1g	1
9				1i	0
Summary

Cell Formulas
Range	Formula
H3	H3	=SUMPRODUCT(N(INDIRECT("'"&rSheets&"'!A1")),rInclude)

Named Ranges
Name	Refers To	Cells
rInclude	=Summary!$L$2:$L$9	H3
rSheets	=Summary!$K$2:$K$9	H3
Sheets	=Summary!$K$2:$K$6	H3

Without the ranges of information but with the relevant arrays Named.

3D Sumif.xlsm
	H
3	36
Summary

Cell Formulas
Range	Formula
H3	H3	=SUMPRODUCT(N(INDIRECT("'"&aSheets&"'!A1")),aInclude)

SUMPRODUCT(N(INDIRECT("'"&aSheets&"'!A1")),aInclude)

 

[Peter_SSs]

I have just used 4 sheets with 2, 4, 6, 8 in A1 and this formula produces 60 which is the result that you would want I think?

BTW, you need to be a bit more careful posting your formulas as your TEXTJOIN function has incorrect syntax in your post.

=SUMPRODUCT(TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(TEXTJOIN(",",1,Sheet1:Sheet4!A1),",","</y><y>")&"</y></x>","//y")),{1,2,3,4})

You can also shorten this a bit by using "</y><y>" directly in the TEXTJOIN instead of commas and then substituting the commas

=SUMPRODUCT(TRANSPOSE(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")),{1,2,3,4})

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]

Thank-you for the response. Quick follow-up question. How would I incorporate an MAX/AVG/MIN functions with this. I would need to caclculate a MAX/AVG/MIN on the resulting product array that results from {x,x,x}{y,y,y}

SUM = SUMPRODUCT(N(INDIRECT("'"&aSheets&"'!A1")),aInclude)
MAX =
AVG =
MIN =

 

[rkaczano]

Sorry just figured it out:

SUM = SUMPRODUCT(N(INDIRECT("'"&aSheets&"'!A1")),aInclude)

MAX =Max(N(INDIRECT("'"&aSheets&"'!A1")) * aInclude)
AVG = AVERAGE(N(INDIRECT("'"&aSheets&"'!A1")) * aInclude)
MIN = MIN(N(INDIRECT("'"&aSheets&"'!A1")) * aInclude)

 

[Dave Patton]

Thanks for the feedback.

 

[Dave Patton]

Did you test the results with your follow-up question?
It is a good idea to
- explain a question clearly
- show an example
- show expected result
- not to over-simplify
- post an extract of your sheet with the forum's tool XL2BB.

The construct that works for SumProduct probably doesn't yield correct answers for your follow-up requirements.

Try the following

3D Sumif.xlsm
	N	O	P
1		Result	Include2
2	Sum	36	1a
3	Average	9	1d
4	Min	2	1f
5	Max	14	1g
6
Summary

Cell Formulas
Range	Formula
O2	O2	=SUMPRODUCT(N(INDIRECT("'"&Sheets2&"'!A1")))
O3	O3	=AVERAGE(N(INDIRECT("'"&Sheets2&"'!A1")))
O4	O4	=MIN(N(INDIRECT("'"&Sheets2&"'!A1")))
O5	O5	=MAX(N(INDIRECT("'"&Sheets2&"'!A1")))

Named Ranges
Name	Refers To	Cells
Sheets2	=Summary!$P$2:$P$5	O2:O5

 

[Peter_SSs]

Wondering if you tried the alternative suggestion that does not require the volatile function INDIRECT?

I have just used 4 sheets with 2, 4, 6, 8 in A1

With these same 4 sheets (named Sheet1 to Sheet4) and now the myArray named range, my formulas would be

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

Cell Formulas
Range	Formula
B1	B1	=SUM(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B2	B2	=MAX(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B3	B3	=MIN(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B4	B4	=AVERAGE(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)

Named Ranges
Name	Refers To	Cells
myArray	=Summary!$E$1:$E$4	B1:B4

 

[rkaczano]

Thank-you, I am still testing both options using volatile (INDIRECT) and non-volatile.

One last question,

If I know that I can calculate the MIN = MIN(N(INDIRECT("'"&aSheets&"'!A1")) * aInclude), how do I incorporate a Conditional Min into that same syntax? That is, I may not want the zero the values produced in the min function that will be naturally generated by the existing conditional logic. Effectively I want to wrap a MINIFS around this function.

MINIFS(min range, criteria_range1, criteria1)

However this does not work as written below:

MINIFS(N(INDIRECT("'"&aSheets&"'!A1")) * aInclude, N(INDIRECT("'"&aSheets&"'!A1")) * aInclude, >0)

Thanks again