INDEX Reference Array Problem

[bahrimian]

Hi everyone,

I have products from different countries with sales in different months. I want to use INDEX formula to sum together only a designated number of months. I have attached a sample excel to better explain.

For example I want to find UK apple sales in last 3 months in column J and worldwide apple sales in the last 3 months in column K. The number of months is designated in the column C. As the array I chose from column D to I, and wanted to use SUMIF+INDEX function to find worldwide fruit sales. However I wasn't able to sum only a part of my array.

Thanks in advance.

[DonkeyOte]

Using your example:

Please Login or Register  to view this content.

Obviously it's hard to know if this is a small set of sample data in context of real dataset... be wary of using SUMPRODUCT en masse or with large ranges.

[bahrimian]

thank you very much.

can you suggest a more feasbile formula to use with large ranges instead of second formula?

[DonkeyOte]

Hmm... well, in truth it really depends on the real problem and size of data / no. of calculations.

SUMPRODUCT (like Arrays) are iterative by nature and thus are *expensive* in terms of overhead.

Is the range dynamic over time or fixed dimension?

How many rows of data will you be applying the SUMPRODUCT too?

Looking at the example I cannot see an elegant solution as such given the variation... but using your sample file to illustrate alternative...

To facilitate standard SUMIF you need the criteria dimension to mirror that of the summation range - i.e. replicate Distributor

Please Login or Register  to view this content.

Your World Total calcs are then *simplified*:

Please Login or Register  to view this content.

So a classic case of Excel-itis insofar as more "light-weight" formulae are often more efficient than fewer "elegant" formulae

[bahrimian]

Thank you again DonkeyOte . For my work is with only about 500 rows, the first SUMPRODUCT formula is ok to go. But i will also consider the second option with large rows.

Best regards.