Stumped by a Sumproduct with Sumif

bodhi808

New Member
Joined
May 20, 2009
Messages
6
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
 

Some videos you may like

Excel Facts

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,987
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,348
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,113,980
Messages
5,545,322
Members
410,676
Latest member
M0J0jojo
Top