# Stumped by a Sumproduct with Sumif

#### bodhi808

##### New Member
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&"'!"&"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

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

#### jasonb75

##### Well-known Member
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

##### Well-known Member
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
BC
11818
1a
Cell Formulas
RangeFormula
B1B1=SUMPRODUCT(SUMIF(INDIRECT("'"&A1:A2&"'!G2:G5"),C2,INDIRECT("'"&A1:A2&"'!H2:H5")))
C1C1=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!G2:G5"),C2,INDIRECT("'"&Sheets&"'!H2:H5")))

Last edited:

Replies
3
Views
1K
Replies
5
Views
352
Replies
0
Views
603
Replies
4
Views
628
Replies
5
Views
7K