Array Formula - Slowing Spread Sheet Down

[mistryman]

Hi Guys

I have this array fomula I am using to count unique values on certain dates. It is dynamically connected to a date cell. When I change the date cell the sheet take a very long time to calculate.

I was looking for suggestions as to how I may speed up the calculation time. I have pasted the formula help.Any help will be greatly appreciated.

SUM(IF(PmtDate=D$9,IF(FREQUENCY(IF(LEN(SecID)>0,MATCH(SecID,SecID,0),""),IF(LEN(SecID)>0,MATCH(SecID,SecID,0),""))>0,1),0))

 

[Faseeh]

Can you upload a sample file!!!

 

[mistryman]

Hi Faseeh

Due to the sensitive nature of the file I am not able to upload it. However the issue persists with many of my other array formulas that seek a unique value then sum the content of another cell. I know I can replicate what I am doing with a Pivot table however it is not as flexible as using formulas in cells especially when building charts.

Do you have any other suggestions ora avenues I can take to mitigate these issues? I am using Excel 2007.

 

[SirJB7]

Hi, mistryman!

Had read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

you'd have found this:
a) http://chandoo.org/forum/threads/new-users-please-read.294/
from where I quote:
"Always

• Be wary that you are posting information on a public web site and that if you post confidential data, it won’t be confidential for long.
• Randomize Numbers and Names if appropriate.

"
and this:
b) http://chandoo.org/forum/threads/posting-a-sample-workbook.451/
from where I quote:
"Important
When Posting data online always remember to anonymise the data, especially names or data if it is commercial or confidential in nature."

So... where's the problem with the confidentiality/privacy? Just have to work a bit.

Regards!