+ Reply to Thread
Results 1 to 4 of 4

Count text formula based on multi-criteria excluding duplicates

  1. #1
    Registered User
    Join Date
    10-08-2014
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2011
    Posts
    43

    Count text formula based on multi-criteria excluding duplicates

    Hi,

    So, I have a solution with an array formula where i can retrieve text based values that exclude duplicates with multiple criteria. How ever, I dump about 5,000 rows of data each week, and the array formula is so slow, and takes around 15 mins to update. I used to used a pivot table, but that wasn't 'automated' either.

    You can find my solution in R2:R8 which is the unique value that I want to retrieve from L:L.

    In the table in T2, I want to do a COUNTIFS based on the values in T:T and U1:AF1.

    Is there any way I can solve this without an array formula?
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count text formula based on multi-criteria excluding duplicates

    Still array formula, Not sure if this faster

    Please try at R2 with CSE and drag down
    =IFERROR(INDEX(L:L,AGGREGATE(15,6,ROW($L$2:$L$1000)/(FREQUENCY(IF($L$2:$L$1000<>"",MATCH($L$2:$L$1000,$L$2:$L$1000,)),ROW($L$1:$L$1000))=1),ROWS(R$2:R2))),"")
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    10-08-2014
    Location
    Stockholm, Sweden
    MS-Off Ver
    Excel 2011
    Posts
    43

    Re: Count text formula based on multi-criteria excluding duplicates

    Thanks! I think generally array formulas are quite slow when it comes to larger data sets. Even though 5,000 rows isn't that much, it's still quite slow to update each week. Wondering if there is another solution than using an array formula (and not a pivot table)

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Count text formula based on multi-criteria excluding duplicates

    How about helper columns

    O2
    =IF(COUNTIFS(L:L,L2)=1,ROW(),"")

    R2
    =IFERROR(INDEX(L:L,SMALL($O$2:$O$5000,ROWS(R$2:R2))),"")
    Attached Files Attached Files

+ 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. Replies: 5
    Last Post: 10-05-2018, 01:26 PM
  2. How to count using multiple criteria and excluding duplicates
    By Carayk in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-08-2018, 01:44 AM
  3. [SOLVED] Count number of lines based on text and number criteria, without duplicates
    By HVCompleto in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2017, 07:17 AM
  4. Count from a text list, excluding duplicates, with multiple criteria
    By Ecervantes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2017, 10:42 PM
  5. Replies: 3
    Last Post: 10-01-2015, 11:09 AM
  6. [SOLVED] Count unique text, excluding space, based on one condition
    By asfa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-01-2013, 02:33 PM
  7. [SOLVED] Formula to count cells between dates excluding duplicates
    By Vegs in forum Excel General
    Replies: 11
    Last Post: 07-05-2006, 02:15 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