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

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
104
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,382
Office Version
  1. 365
Platform
  1. Windows
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’)
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

rkaczano

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,382
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1365365 & 2019
2SUM16161
3MAX880
4MIN221
5AVERAGE5.3333333335.3333333331
Summary
Cell Formulas
RangeFormula
B2B2=SUM(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
C2C2=SUM(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*myArray)
B3B3=MAX(FILTER(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y"),myArray=1))
C3C3=AGGREGATE(14,6,FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*1/myArray,1)
B4B4=MIN(FILTER(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y"),myArray=1))
C4C4=AGGREGATE(15,6,FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y")*1/myArray,1)
B5B5=AVERAGE(FILTER(FILTERXML("<x><y>"&TEXTJOIN("</y><y>",1,Sheet1:Sheet4!A1)&"</y></x>","//y"),myArray=1))
C5C5=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
NameRefers ToCells
myArray=Summary!$E$2:$E$5B2:C5
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,238
Messages
5,836,186
Members
430,406
Latest member
pmav

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
Top