CODEWITHSUNDEEP
excelexcel-formula
Ricky Drennan
Ricky Drennan

Reputation: 55

Using Indirect with SumProduct

Following this question, I am trying to implement the SUMPRODUCT(ABS()) formula to include an INDIRECT. The reason for this is that I want to run the calculation for each combination of employees. Row 2 now contains a list of all employees and column A contains the same list of all employees. I'm attempting the following formula in cell B3. I am getting a #VALUE error:

=SUMPRODUCT(ABS(INDIRECT("Sheet1!J"&ROW(A3)-1&":BB"&ROW(A3)-1)-INDIRECT("Sheet1!J"&COLUMN(B2)&":BB"&COLUMN(B2))))

Upvotes: 0

Views: 179

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

ABS in older version outside Office 365 does not like to use arrays and as such we must "force" it to use arrays with N().

Also avoid INDIRECT if possible as it is volatile and will cause calc slow down.

=SUMPRODUCT(ABS(N(INDEX(Sheet1!J:BB,ROW(A3)-1,0)-INDEX(Sheet1!J:BB,COLUMN(B2),0))))

Upvotes: 1

Related Questions