Decoding an excel formula on sumproduct

Hi PoonamKhatri,

There's an error on your formula that you post on the thread...so a little correction here and also the explanation of the SUMPRODUCT formula.

SUMPRODUCT(Inventory!C$3:Inventory!C7,INDIRECT(ADDRESS(ROW(FC!$A$2006)-Inventory!$A7+1,COLUMN(FC!B$4),,,"FC")):FC!B$2006)
This INDIRECT formula basically turns into FC!$B$2002

Breakdown of formula and it's result:

Inventory!C$3:Inventory!C7: Look at the range of C3:C7 in [Inventory] sheet. Result in {5;10;10;10;10}

ROW(FC!$A$2006): Returns the row index in cell A2006 in [FC] sheet. Result in 2006 (because A1 is row 1, A100 is row 100, thus A2006 is 2006)

COLUMN(FC!B$4): Returns the column index in cell B4 in [FC] sheet. Result in 2 (because column A is 1, column B is 2)

ADDRESS function has the following properties:
ADDRESS(row_num, column_num, [abs_num],[a1], [sheet_text]) - here row_num and column_num is mandatory, and the rest are optional.

Given that
ADDRESS(ROW(FC!$A$2006)-Inventory!$A7+1,COLUMN(FC!B$4),,,"FC"), and we know that the ROW() and COLUMN(), it thus becomes
ADDRESS(2006-Inventory!$A7+1,2,,,"FC") which simplifies further to
ADDRESS(2006-5+1,2,,,"FC") which simplifies further to
ADDRESS(2002,2,,,"FC").
And finally reads as "FC!$B$2002". Note that at this point this is only a text or string. Not an Excel Range.

This then gets put into the INDIRECT function:
INDIRECT(ADDRESS(...)) technically ask INDIRECT function to turn the string that we derived from ADDRESS function into an excel range.

And finally it turns into a range that is used for the SUMPRODUCT:

SUMPRODUCT(Inventory!C$3:Inventory!C7, INDIRECT("FC!$B$2002"):FC!B$2006) notice that double quote
SUMPRODUCT(Inventory!C$3:Inventory!C7, FC!$B$2002:FC!B$2006) and thus becomes
{5;10;10;10;10} * {1.38E-15; 1.43E-15, 1.5E-15, 1.59E-15, 1.69E-15}