# Help speed/clean up frequency formula

1. ## Help speed/clean up frequency formula

Hi,

My formula is counting unique values based on multiple criteria and I've used the frequency formula but I believe because my data set is so large it makes my array huge and so it takes a lot of time and memory to process the formula. Is there a better way to write this formula or calc something off my data set to help? Thanks!!

=SUM(IF(FREQUENCY(IF('CC Data Translation'!\$C:\$C=\$B47,IF('CC Data Translation'!\$M:\$M=BW\$11,MATCH('CC Data Translation'!\$L:\$L,'CC Data Translation'!\$L:\$L,IF('CC Data Translation'!\$M:\$M=BX\$11,MATCH('CC Data Translation'!\$L:\$L,'CC Data Translation'!\$L:\$L,IF('CC Data Translation'!\$M:\$M=BY\$11,MATCH('CC Data Translation'!\$L:\$L,'CC Data Translation'!\$L:\$L,0))))))),ROW('CC Data Translation'!\$L\$5:\$L\$80000)-ROW('CC Data Translation'!\$L\$5)+1),1))  Register To Reply

2. ## Re: Help speed/clean up frequency formula

Don't use full column references. Use specific ranges, or Dynamic Named Ranges ... or maybe a Structured Table.  Register To Reply

3. ## Re: Help speed/clean up frequency formula

Once you revise your ranges, per TMS, you can also consider consolidating your "OR" M-range tests given the contiguous criteria range

=SUM(IF(FREQUENCY(IF(C-range=x,IF(ISNUMBER(MATCH(m-range,BW\$11:BY\$11,0)),MATCH(L-range,L-range,0))),ROW(..)-ROW(...)+1),1))

whilst the above will have a negligible impact on performance it will shorten / simplify the formula itself.

if you have access to the FILTER & UNIQUE functions, given your O365 subsc, you might consider looking into that...

I don't unfortunately but, if we assume these will mimic the GoogleSheets equivalent then something like below might work?

=COUNTIF(UNIQUE(FILTER(L-range,(C-range=x)*ISNUMBER(MATCH(M-range,BW\$11:BY\$11,0)))),"?*")

and, if that's true, I would hope the above will prove (far) more efficient with large ranges than an iterative Array.  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Tags for this Thread #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1