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