How to convert Array formula into Regular one to avoid long calculation time

[ibraam]

Hi everyone, in the attached example, in cell (E11), I want to count unique customers which purchased product 1, their area code matches with the corresponding code in cell (D11), and only purchased less than 5 units

I use the following array formula:

=SUM(IF(FREQUENCY(IF($G$2:$G$7=D11,IF($I$2:$I$7="Product 1",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))

It works perfectly, but when using this formula through huge database which contains tons of customers, product& areas. Excel take very long time to calculate this function in a table column consists of +1000 cells, It takes about 10 minutes to calculate only one column which is possible to continue like that

Is there any way to convert this Array function into regular one by using helper columns or whatever to avoid all this time waiting for calculations

Any help will be appreciated to the maximum, thanks a lot in advance

[MarvinP]

Hi ibraam and welcome to the forum,

This looks like it could be a Pivot Table answer. Can you attach your sample workbook instead of a picture so I can try to Pivot it and see if our results match?

[ibraam]

Hi ibraam and welcome to the forum,

This looks like it could be a Pivot Table answer. Can you attach your sample workbook instead of a picture so I can try to Pivot it and see if our results match?

First thanks a lot for your reply, would you kindly find my attached example excel sheet ... I Edited mu subject to attache it

[MarvinP]

Hi,

See if the attached Pivot Table does what you need. See if it is much faster. I filtered by Customer and Product. Then only by Area Code.
I did a count of products and you can filter by that number using a Values Filter in the Row Label dropdown.

Pivot instead of Array Formula.xlsx

[ibraam]

Hi,

See if the attached Pivot Table does what you need. See if it is much faster. I filtered by Customer and Product. Then only by Area Code.
I did a count of products and you can filter by that number using a Values Filter in the Row Label dropdown.

Attachment 471956

thanks a lot for your appreciated help, at the same time i have to choose numbers less than 5 after filtering sales field, i:e I have to choose -2, 1, 2 manually. when dealing with huge date I may spent all day& night to mark specific sales values and ignore others. Is there any way to make the conditions (count unique customers if less than 5 ) or ( count unique customers if more than 5) an automatic option in my pivot table? .... Are You get it ?

[MarvinP]

I think my Pivot Table will do that if you use the Value Filter in Pivot Tables.

See the picture at http://www.contextures.com/excel-piv...ers-top10.html

See the part about "Value Filters".

[ibraam]

I think my Pivot Table will do that if you use the Value Filter in Pivot Tables.

See the picture at http://www.contextures.com/excel-piv...ers-top10.html

See the part about "Value Filters".

Well. it works , thanks a lot. but the bigger challenge now that my original worksheet consists of 12 sheets referring to 12 months of a year named (Jan, Feb, Mar, ..... etc) and an extra sheet which is called analyzer in which i write my arrays formulas referring to January sheet as example when copying January sales report which has specific format i know very well and prepare my analyzer to match to, it will automatically automate everything and return tables and charts to my dashboard ... but now, I have to manually insert a pivot table to each month report separately with its specific range which may be different than other months reports range ... and do filters manually for each month and ... etc, the idea behind my first question is AUTOMATION, is there any way to automate every thing using pivot table once copying month report to its sheet on my analyzer workbook ? ... r u get the idea behind all this ... THAT WILL NOT MAKE ME NOT SAYING THANKS A LOT ANYWAY ")

[kvsrinivasamurthy]

Is it possible to sort the individual sheets (Eg: January, February, etc) on G column. Then it is possible to reduce time.

[ibraam]

Is it possible to sort the individual sheets (Eg: January, February, etc) on G column. Then it is possible to reduce time.

Sorry, I can't understand what do you aiming for, any clarification will be appreciated

[kvsrinivasamurthy]

Now the Entire range(rows) is used. If data is sorted on G column the range can be reduced to rows having the required area code .

[JohnTopley]

I did a "volume" test for one month as follows:

1177 rows of data, consisting of 200 customers, 27 areas, 50 products.

With an output table of 27 Areas (rows) and 50 products (columns) the results were returned almost instantaneously.

a table column consists of +1000 cells, It takes about 10 minutes

Does this test reflect the above situation?