Decoding an excel formula on sumproduct

Hello Everybody,

This is my first post in this forum and I need help with an excel formula in a worksheet. It is a 'sumproduct' but I am not able to decode which rows and columns are linked in the formula because of the 'indirect function' used. The formula is:

Sumproduct(Inventory!C$3:InventoryC7,INDIRECT(ADDRESS(FC!$A$2006)-Inventory!$A7+1,Column(FCB$4),,,"FC")):FC!B$2006)

Here sumproduct in a sheet is calculated using data from two sheets 'inventory' and 'FC'.

I would be really grateful if someone can help me in understanding which rows of 'FC' sheet are multiplied with the rows C3:C7 in the 'Inventory' sheet.

Kind regards,
Poonam

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

Administrative Note:

We don't expect you to bump threads sooner than 24 hours after your last post or more than once a day. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. If you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

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.

Please Login or Register  to view this content.

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}

It strikes me that it's a very messy formula!!

To help you understand, I have CUT IT BACK to a sample (10-20 rows, as requested) and simplified it with small integers.

In the first row it is multiplying the first ONE value on Inventory by the last value on FC and summing the result (as there's only one result... that doesn't happen... obviously)
In the second row it is multiplying the first TWO values on Inventory by the last TWO values on FC and summing the result
In the third row it is multiplying the first THREE values on Inventory by the last THREE values on FC and summing the result
and so on.

It is HORRIBLY inefficient.

Use this instead:

=SUMPRODUCT(Inventory!C$3:Inventory!C3,FC!$B$26:INDEX(FC!$B:$B,AGGREGATE(14,6,ROW(FC!$B$7:$B$26),COUNT($A$4:A4))))

change the bits in red to reflect the last data row in your real sample. Drag down.

With a small sample (now...) you can use formulas/formula auditing/evaluate formula to see what is happening. That is impossible with 2000+ rows.