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
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