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

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
141
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
HIJKL
1Total and by Sheet SumifSheets
21a1
3361b0
4361c0
51d1
61e0
71f1
81g1
91i0
Summary
Cell Formulas
RangeFormula
H3H3=SUMPRODUCT(N(INDIRECT("'"&$K$2:$K$9&"'!A1")),L2:L9)
H4H4=SUMPRODUCT(N(INDIRECT("'"&{"1a";"1b";"1c";"1d";"1e";"1f";"1g";"1i"}&"'!A1")),{1;0;0;1;0;1;1;0})
Named Ranges
NameRefers ToCells
Sheets=Summary!$K$2:$K$6H3
 
Upvote 0
3D Sumif.xlsm
HIJKL
1Total and by Sheet SumifSheetsInclude
21a1
3361b0
41c0
51d1
61e0
71f1
81g1
91i0
Summary
Cell Formulas
RangeFormula
H3H3=SUMPRODUCT(N(INDIRECT("'"&rSheets&"'!A1")),rInclude)
Named Ranges
NameRefers ToCells
rInclude=Summary!$L$2:$L$9H3
rSheets=Summary!$K$2:$K$9H3
Sheets=Summary!$K$2:$K$6H3


Without the ranges of information but with the relevant arrays Named.
3D Sumif.xlsm
H
336
Summary
Cell Formulas
RangeFormula
H3H3=SUMPRODUCT(N(INDIRECT("'"&aSheets&"'!A1")),aInclude)


SUMPRODUCT(N(INDIRECT("'"&aSheets&"'!A1")),aInclude)
 
Last edited:
Upvote 0
Solution
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. :)

Excel Formula:
=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

Excel Formula:
=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’)
 
Upvote 0
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 =
 
Upvote 0
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)
 
Upvote 0
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
NOP
1ResultInclude2
2Sum361a
3Average91d
4Min21f
5Max141g
6
Summary
Cell Formulas
RangeFormula
O2O2=SUMPRODUCT(N(INDIRECT("'"&Sheets2&"'!A1")))
O3O3=AVERAGE(N(INDIRECT("'"&Sheets2&"'!A1")))
O4O4=MIN(N(INDIRECT("'"&Sheets2&"'!A1")))
O5O5=MAX(N(INDIRECT("'"&Sheets2&"'!A1")))
Named Ranges
NameRefers ToCells
Sheets2=Summary!$P$2:$P$5O2:O5
 
Upvote 0
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
ABCDE
1SUM161
2MAX80
3MIN01
4AVERAGE41
Summary
Cell Formulas
RangeFormula
B1B1=SUM(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B2B2=MAX(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B3B3=MIN(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B4B4=AVERAGE(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
Named Ranges
NameRefers ToCells
myArray=Summary!$E$1:$E$4B1:B4
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top