+ Reply to Thread
Results 1 to 3 of 3

Help speed/clean up frequency formula

  1. #1
    Registered User
    Join Date
    04-24-2020
    Location
    San Francisco, CA
    MS-Off Ver
    365 proplus
    Posts
    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))

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,219

    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.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,078

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to clean up list
    By Allen_dulles in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-30-2016, 05:07 AM
  2. Clean up and Speed Up Code
    By Mad-Dog in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-11-2015, 04:54 AM
  3. [SOLVED] Coding Convention Questions #2 (To clean up or not to clean up)
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 03-31-2014, 08:18 PM
  4. Replies: 3
    Last Post: 02-11-2014, 05:36 PM
  5. [SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol
    By tracylsr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2012, 04:21 PM
  6. Clean Function to Clean Entire Sheet
    By gema in forum Excel General
    Replies: 7
    Last Post: 11-05-2009, 10:07 AM
  7. CLEAN Formula
    By Kristen in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-12-2005, 07:07 PM

Tags for this Thread

Bookmarks

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