Stumped by a Sumproduct with Sumif

[bodhi808]

Hi there, I hope someone can help me with this brainteaser.
I have a number of sheets with identical format and am trying to look in a series of columns for a value and then use that to sum an adjacent column.

The formula I have works but I'm certain uses a lot more memory and time to calculate than it needs to and could be far more elegant!
The problem arises because I am trying to compare the criteria to multiple columns across multiple work sheets. I had hoped that I could use an array with both rows and columns as highlighted here.

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"T3:AF100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))

("sheets" is a list of worksheet names that the formula looks in, 9 in total)
However it only returns results where the criteria is matched in column T and not U through AF.

So I ended up with the following monstrosity looking in each column separately.

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"T3:T100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"u3:u100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"v3:v100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"w3:w100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"x3:x100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"y3:y100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"z3:z100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"aa3:aa100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"ab3:ab100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"ac3:ac100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"ad3:ad100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"ae3:ae100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))
+SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"af3:af100"),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))

Is there change of syntax needed to expand the array successfully to include all columns or is it the Sumif which is limiting in this instance?

Thanks
Chris

 

[jasonb75]

There is no way of writing such a formula efficiently, you might be able to shorten the formula with use of OFFSET but it would most likely be even less efficient than the existing formula.

In theory the formula would be something like

=SUMPRODUCT(SUMIF(OFFSET(INDIRECT("'"&sheets&"'!"&"T3:T100"),0,COLUMN(T3:AF3)-COLUMN(T3)),E14,INDIRECT("'"&sheets&"'!"&"AH3:AH100")))

but I don't have a suitable file to hand for testing that.

 

[Dave Patton]

You can ignore this post. I could not delete it.

I provided a version of the formula that works but it is not as complex as your challenge.
It shows with the factors relevant in my example the formula formula works and the syntax is correct.

I show two versions of the same formula below; this workbooks in my workbook.
You can review the syntax.
You can create a mini example to test the formula.
Hopefully, this helps.

T10_1710a.xlsm
	B	C
1	18	18
1a

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A2&"'!G2:G5"),C2,INDIRECT("'"&A1:A2&"'!H2:H5")))
C1	C1	=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!G2:G5"),C2,INDIRECT("'"&Sheets&"'!H2:H5")))