# How to replace an array excel formula with another regular one to avoid long calculation time

EXAMPLE

by using an array formulas in range (E11:E16) According to the above example:

To count unique customers in range (E2:E7) that meet only the following criteria:

1- Purchased Product 1, regardless either they purchased any other products or not

2- Purchased less than 5 units after sum. if there is any repeated customers

3- Their areas codes matche corresponding code in range (D11:D16)

I Use the following array formula to be in E11:

``````=SUM(IF(FREQUENCY(IF(\$G\$2:\$G\$7=D11,IF(\$I\$2:\$I\$7="Product1",IF(\$J\$2:\$J\$7<5,IF(\$E\$2:\$E\$7<>"",MATCH(\$E\$2:\$E\$7,\$E\$2:\$E\$7,0))))),ROW(\$E\$2:\$E\$7)-ROW(G2)+1),1))
``````

This formula is doing great, at the same time when using it thru very huge database containing tons of rows and columns, excel takes a bout 3 minutes to calculate only one cell which is terrible to continue like that

is there any way to convert this array formula to regular one ... any help will be appreciated to the maximum ... Thanks in advance

• Reposting it on this site from Stack Overflow will probably get the same results as many who frequent this site frequent the other site as well. You will need vba. Jul 24, 2016 at 15:40
• Your #2 line appears incomplete: "if there is any repeated customers" Jul 26, 2016 at 23:46
• I'm confused by your EXAMPLE - why do you repeat the same Area Code on rows 15 & 16? Jul 26, 2016 at 23:48
• Posting the same stuff on multiple sites across the network is generally frowned upon. Please do not do this.
– undo
Jul 31, 2016 at 7:40

I would use Power Query for this. Power Query is a free Add-In from Microsoft for Excel 2010 and 2013, and is built into Excel 2016 on the Data ribbon, under Get & Transform.

I'm not sure of your definition of "tons", but Power Query scales up to around 10 million of input rows fairly comfortably. If your data source is actually a database (e.g. SQL Server, Oracle etc), Power Query shifts as much of the work as it can off to the database.

For your challenge, I would start by creating a Query using the From Table button to read from the 1st table, and choose Close and Load / Close and Load To, then choose Only Create Connection. If your source data actually comes from a real database or other file, it will be more efficient to point Power Query directly at that source instead.

Then I would start the actual output Query from the list of area codes in range D11:D16, using the From Table button.

I would add a Merge step to join to the 1st Query on Area Code, then expand all it's columns.

Then I would add a Conditional Column called Product 1, specifying: if (column) Product equals (value) Product 1 then (column) Product

Then I would add a Group By step, grouping on Area Code and Customer, and aggregating the Sum of Sales and Max of Product 1.

Then I would filter the Product 1 column to just the rows with a value of "Product 1", then filter the Sales column to less than 5.

Finally I would another Group By step, grouping on Area Code , and aggregating the default Count.

By default Power Query will deliver this into a new Table on a new Workbook - probably best to leave it there to avoid clashes with other objects.

When your source data changes, just Refresh the query to regenerate the output table.