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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
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,931
Messages
5,545,091
Members
410,652
Latest member
Zot
Top